1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Date format issue

Discussion in 'SQL PL/SQL' started by Durbslaw, Feb 13, 2019.

Tags:
  1. Durbslaw

    Durbslaw Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    South Africa
    Hi Group,

    Thanks for reading and replying where applicable...I have a query that accepts and compares and date input to another...

    snipet the works...and expects date in DD/MON/YYYY format

    not_before_date between
    (
    case
    when to_date:)pdate1) <> '31/DEC/9999' then to_date:)pdate1)

    end
    )
    and
    (
    case

    when to_date:)pDate6) <> '31/DEC/9999' then to_date:)pDate6)
    end

    )

    Howerver my requirement is the input should be in YYYY-MM-DD format...I have tried...

    not_before_date between
    (
    case
    when to_date:)pdate1,'YYYY-MM-DD') <> '9999-12-31' then NULL

    end
    )
    and
    (
    case
    when to_date:)pdate2,'YYYY-MM-DD') <> '9999-12-31' then NULL

    end


    )
    This reults in a error...
    ORA-01847: day of month must be between 1 and last day of month

    01847. 00000 - "day of month must be between 1 and last day of month"
    *Cause:
    *Action:
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    778
    Likes Received:
    149
    Trophy Points:
    830
    Location:
    Russian Federation

    You should always use explicit conversions.

    You should always use explicit transformations.
    Using implicit conversions - the current settings of the NLS session
    will be used and there may be different "surprises"...

    Be helpful:
    1) data type conversion rules
    2) Datetime Format Models

    Example:

    Code (Text):


    select
      to_date (dt,'yyyy-mm-dd') d,
      nullif(to_date (dt,'yyyy-mm-dd'),date '2017-12-31') compare_d
    from
    (
      select
      '201'||level||'-12-31' dt
      from
        dual
      connect by level<=7
    );
     
     
    Last edited: Feb 13, 2019
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,684
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As mentioned previously you need to use explicit conversions since the default value for NLS_DATE_FORMAT may not be what you expect:

    Code (SQL):
    VALUE
    ----------------------------------------------------------------
    DD-MON-RR


    PL/SQL PROCEDURE successfully completed.

    SYS @ orcl >
    SYS @ orcl > SELECT
      2  CASE
      3  WHEN to_date(:pdate1,'RRRR-MM-DD') <> '9999-12-31' THEN NULL
      4  END
      5  FROM dual;
    ERROR:
    ORA-01847: DAY OF MONTH must be BETWEEN 1 AND LAST DAY OF MONTH



    no ROWS selected

    SYS @ orcl >
    SYS @ orcl > SELECT
      2  CASE
      3  WHEN to_date(:pdate2,'RRRR-MM-DD') <> '9999-12-31' THEN NULL
      4  END
      5  FROM dual;
    ERROR:
    ORA-01847: DAY OF MONTH must be BETWEEN 1 AND LAST DAY OF MONTH



    no ROWS selected

    SYS @ orcl >
    SYS @ orcl > SELECT
      2  CASE
      3  WHEN to_date(:pdate1,'RRRR-MM-DD') <> to_date('9999-12-31','RRRR_MM-DD') THEN NULL
      4  END
      5  FROM dual;

    C
    -


    SYS @ orcl >
    SYS @ orcl > SELECT
      2  CASE
      3  WHEN to_date(:pdate2,'RRRR-MM-DD') <> to_date('9999-12-31' ,'RRRR_MM-DD') THEN NULL
      4  END
      5  FROM dual;

    C
    -


    SYS @ orcl >