Search

Top 60 Oracle Blogs

Recent comments

Automatic date formats

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