Why relying on default DATE formats is a bug

Today’s quick but important message is…don’t be lazy!

Look….I get it

If given the choice of typing:

'01-JUL-20'

or typing this:

to_date('01-JUL-20','DD-MON-RR')

then more often than not, we all are going to opt for the first alternative because we all can get lazy. But that reliance on the default date format mask in a session can be fraught with danger. Because a script that worked in one circumstance, can silently fail in another.

Here is how easy it is to fall into this trap.  I’ll start with the expected experience when performing the most simple of operations – just inserting a row:

SQL> create table t ( id int, x date );

Table created.

SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered.

SQL> insert into t values ( 1,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01

But what happens if someone has changed the client default settings for NLS_DATE_FORMAT. This could have been easily pushed out via a registry change to your workplace windows machine, or an bash profile change on your Linux machine. And lets face it – how often do we go back and double check those settings? I’ll repeat the same insert with modified NLS settings.

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> insert into t values (2,'01-JUL-20');

1 row created.

This is one of those examples where the database is almost too “generous” with trying to help us. The format mask is expecting 4 digits for the year; we offered only 2, and the database responded with “No problems!”

No errors, no warnings…until we look at the data.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01

If you really do not like using explicit TO_DATE functions whenever you are referring to literal values for dates, at least please use the DATE constructor function, which only allows a single (valid) format of YYYY-MM-DD.

SQL> insert into t values (3,date '2020-07-01');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01
         3 2020-07-01