Implicit conversion of a character string to Oracle DATE type is dangerous

Typically TO_DATE function is used by taking a char as the first argument and 'fmt' as the second argument. According to Oracle doc:

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is J, for Julian, then char must be an integer.

It should be pointed out that omitting the fmt could be a very bad practise. If the character string does not match the default format, Oracle does not necessarily throw error in some cases. For example, I have executed the following commands in the SQL* Plus:

SQL>alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';

Session altered.


SQL>select count(*) from  dba_objects where created between '1-Nov-2011' and '14-Nov-2011';

  COUNT(*)
----------
         0

1 row selected.

SQL>select to_date('1-Nov-2011') from dual;

TO_DATE('1-NOV-2011
-------------------
0001-11-20 11:00:00

1 row selected.

While the correct results should be as follows:

SQL>alter session set nls_date_format='dd-Mon-yyyy HH24:mi:ss';

Session altered.

SQL>select count(*) from  dba_objects where created between '1-Nov-2011' and '14-Nov-2011';

  COUNT(*)
----------
       171

1 row selected.

SQL>select to_date('1-Nov-2011') from dual;

TO_DATE('1-NOV-2011'
--------------------
01-Nov-2011 00:00:00

1 row selected.

I have learned this in a pretty hard way during a sql tuning effort. Many hours have been spent before I realized that the weired results were due to this implicit conversion "bug". ( See http://www.freelists.org/post/oracle-l/Huge-difference-between-sqlplus-and-sqldeveloper-sorting-in-memory-vs-disk,7)