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 
Leave a Reply