I'm always using proper format for to_date function. However I found that to_date works on even when format doesn;t match the string. Please see below:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


SQL> show parameter nls_date

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-RR
nls_date_language string AMERICAN


SQL> create table testtest (d date);
Table created.

-- Case #1, correct format
SQL> insert into testtest values (to_date('2015-09-22 23:19:00','YYYY-MM-DD HH24:MI:SS'));

1 row created.

-- Case #2, two dashes removed
SQL> insert into testtest values (to_date('20150923 23:20:00','YYYY-MM-DD HH24:MI:SS'));

1 row created.

-- Case #3, all dashes, spaces, colons removed
SQL> insert into testtest values (to_date('20150924233000','YYYY-MM-DD HH24:MI:SS'));

1 row created.

-- Case #4, only date portion in YYYYMMDD format remains
SQL> insert into testtest values (to_date('20150925','YYYY-MM-DD HH24:MI:SS'));

1 row created.

SQL> select to_char(d,'YYYY-MM-DD HH24:MI:SS') from testtest;

TO_CHAR(D,'YYYY-MM-
-------------------
2015-09-22 23:19:00
2015-09-23 23:20:00
2015-09-24 23:30:00
2015-09-25 00:00:00

4 rows selected.


I was expecting to get errors in cases 2-4, but all statements work ok, why?

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>