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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s