Dates in 8i


The following have been tested on 8.1.5 and 8.1.6. Notice that the date string does not match the format mask. In previous releases of Oracle, these would yield an error (you could argue that this is still what should be happening!). I'll post any other combinations I find that work where they should not.

Update 31/07: Simple solution to this problem is to read the documentation. When a date conversion fails, Oracle tries some similar formats to try succeed. This table is in the Format Model section of the SQL Reference

Original Format Element Additional Format Elements to Try if Original fails
'MM''MON' and 'MONTH'
'MON''MONTH'
'MONTH''MON'
'YY''YYYY'
'RR''RRRR'
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

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>