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 ddmonyyy ?

Discussion in 'SQL PL/SQL' started by David Maertens, Jan 19, 2016.

  1. David Maertens

    David Maertens Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Arnold, Maryland
    Anyone know why this works (3 digit year).
    It might sound strange but I actually would like to see it error out.

    select to_date('18SEP201','ddmonyyyy') from dual;
    TO_DATE('18SEP201','DDMONYYYY')
    -------------------------------
    18-SEP-01
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Using the default date display format doesn't show what is really going on; changing it to report all four digits of the year gives a clearer picture:

    SQL> alter session set nls_date_format = 'DD-MON-RRRR';

    Session altered.

    SQL> select to_date('18SEP201','ddmonyyyy') from dual;

    TO_DATE('18
    -----------
    18-SEP-0201

    SQL>

    You're not getting Oracle to think it's a partial string for any year from 2010 on, you're telling Oracle it's 201, a valid year in the internal calendar. As such leading zeros need not be included and there is no reason to throw an error. You might want to read this post:

    https://dfitzjarrell.wordpress.com/...-and-pope-gregory-xiii-have-in-common-oracle/

    and this one:

    https://dfitzjarrell.wordpress.com/2008/06/03/how-about-a-date/
     
    Last edited: Jan 19, 2016