Learning is not a spectator sport

Automatic date formats

1–2 minutes

Notice in all of the examples below that the date string does not match the format mask.

When a date conversion fails, Oracle tries some similar formats to try succeed. This is actually documented as well here

 

Original Format Element Additional Format Elements to Try if Original fails
‘MM’ ‘MON’ and ‘MONTH’
‘MON’ ‘MONTH’
‘MONTH’ ‘MON’
‘YY’ ‘YYYY’
‘RR’ ‘RRRR’

 


SQL> select to_date('01JAN2000','ddmmyyyy') from dual;

TO_DATE('
---------
01-JAN-00

SQL> select to_date('01JAN1999','ddmmyyyy') from dual;

TO_DATE('
---------
01-JAN-99

SQL> select to_date('01JAN2000','dd/mm/yy') from dual;

TO_DATE('
---------
01-JAN-00

SQL>  select to_date('01JAN2000','dd-mm-yyyy') from dual;

TO_DATE('
---------
01-JAN-00

SQL>

Needless to say, relying on any kind of implied format for dates (or any other datatype for that matter) is generally going to get you intro strife eventually Smile

One response to “Automatic date formats”

  1. Why Why Why Why? | Oracle Scratchpad

    […] to date was ‘dd-mm-yyyy’. Oracle tries quite hard to cope with date conversions, as Connor McDonald pointed out several years […]

    Reply

Got some thoughts? Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trending