Say NO to default DATE formats

Posted by

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

Look….I get it Smile

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!” Smile

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

2 comments

  1. It’s cool to demonstrate the hottest and newest features. This example shows it can be as cool to demonstrate features available since as early as Oracle 9i!

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.