Oracle Tips and Tricks — David Fitzjarrell

August 29, 2019

It’s A Date?

Filed under: General — dfitzjarrell @ 16:09

"You often learn more by being wrong for the right reasons
than right for the wrong reasons." 
― Norton Juster, The Phantom Tollbooth

Dates. The term can conjur fear in the strongest hearts, mainly due to problems that arise when date formats are taken for granted. Not every Oracle database is configured with the ‘default’ Oracle-supplied date format, DD-MON-RR. Since this setting can be changed at both the database level and the session level it can be rather confusing when scripts aren’t working properly. Let’s see what can go horribly wrong when date formats are taken for granted or ignored.

Implicit conversion is the most insidious in my opinion as it relies on the declared format for the database or the session, and that is defined by the NLS_DATE_FORMAT parameter. Date strings come in all shapes and sizes but for an Oracle database the format it’s expecting may not be the format the string is in. Given this NLS_DATE_FORMAT setting:.


DD-MON-RRRR

any attempt to implicitly convert ‘1999-01-31’ to an Oracle date will fail:


SQL> select * From dba_Objects where created = '1999-01-31';
select * From dba_Objects where created = '1999-01-31'
                                          *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

If the string is put into a more … ‘normal’ … format a different error appears:


SQL$gt; select * From dba_Objects where created = '01-01-1999'
  2  /
select * From dba_Objects where created = '01-01-1999'
                                          *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

To avoid such errors best practice is to always use TO_DATE() to convert strings into date values and always supply a matching format string. It is not a good practice to rely on session or database settings even if you KNOW the formats are the same. Someone else that might execute that script may not have set the same date format used in your session, and mystically the script fails to execute with one of the above errors. As another example let’s set the NLS_DATE_FORMAT in a session to a known string and perform some conversions. The session date format will be changed twice; for some conversions either format provides error-free results but when the month and day are reversed some conversions will complete but the month portion of the date could be a surprise::


SQL> --
SQL> -- Set the session date format
SQL> -- the same as the string we are trying
SQL> -- to convert
SQL> --
SQL> -- Notice the month is first in the format
SQL> -- string
SQL> --
SQL> alter session set nls_date_format = 'MM-DD-RRRR';

Session altered.

SQL>
SQL> --
SQL> -- Try implicit conversion again
SQL> --
SQL> -- Return the month name as well
SQL> --
SQL> -- This works but it's 'iffy'
SQL> --
SQL> -- Relying on the default format is
SQL> -- not a wise idea
SQL> --
SQL> select '01-01-1999' from dual
  2  where sysdate >= '01-01-1999';

'01-01-199
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Another attempt using TO_DATE()
SQL> -- without a format string
SQL> --
SQL> -- Again, this works but it's 'iffy'
SQL> --
SQL> select to_date('01-01-1999') from dual;

TO_DATE('0
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Use a properly constructed TO_DATE()
SQL> -- statement
SQL> --
SQL> -- This works without exception
SQL> --
SQL> select to_date('01-31-1999', 'MM-DD-RRRR') from dual;

TO_DATE('0
----------
01-31-1999

SQL>
SQL> --
SQL> -- Change the session format yet again
SQL> --
SQL> -- This time the month is not first
SQL> -- so conversions where the 'day' is 12
SQL> -- or less will still work with this format
SQL> --
SQL> alter session set nls_date_format = 'DD-MM-RRRR';

Session altered.

SQL>
SQL> --
SQL> -- Change the date string to convert
SQL> --
SQL> -- Even with the month and day reversed the
SQL> -- following conversions, implicit and unformatted,
SQL> -- work because there is a month number 1 in
SQL> -- the year
SQL> --
SQL> -- This won't throw any errors until the day
SQL> -- is greater than 12
SQL> --
SQL> -- In this case the month is what we expect
SQL> --
SQL> select '01-01-1999' from dual
  2  where sysdate >= '01-01-1999';

'01-01-199
----------
01-01-1999

SQL>
SQL> select to_date('01-01-1999') from dual;

TO_DATE('0
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Let's make the 'day' value 11
SQL> -- and see what date results
SQL> --
SQL> -- We won't get the month we
SQL> -- expect
SQL> --
SQL> select '01-11-1999' from dual
  2  where sysdate >= '01-11-1999';

'01-11-199
----------
01-11-1999

SQL>
SQL> select to_char(to_date('01-11-1999'),'Month') from dual;

TO_CHAR(T
---------
November

SQL>
SQL> --
SQL> -- Now the implicit and unformatted
SQL> -- conversions fail
SQL> --
SQL> -- The default format switched the month
SQL> -- and day in the string
SQL> --
SQL> -- The Gregorian calendar doesn't have
SQL> -- 31 months in a year
SQL> --
SQL> select '01-31-1999' from dual
  2  where sysdate >= '01-31-1999';
where sysdate >= '01-31-1999'
                 *
ERROR at line 2:
ORA-01843: not a valid month


SQL>
SQL> select to_date('01-31-1999') from dual;
select to_date('01-31-1999') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>
SQL> --
SQL> -- The formatted conversion still works
SQL> --
SQL> select to_date('01-31-1999', 'MM-DD-RRRR') from dual;

TO_DATE('0
----------
31-01-1999

SQL>

Not knowing the pre-set NLS_DATE_FORMAT can wreak havoc with implicit date conversions, and do the same when TO_DATE() is used without a format specifier. Depending upon the format string it may take a while for Oracle to throw an error and the reason may seem to be a mystery. Not many applications return the month name from a date value so when the format starts ‘DD-MM’ rather than ‘MM-DD’ errors won’t appear for date strings similar to ’01-01-2020′ until day 13 of the month, leaving developers to scratch their heads in disbelief and confusion.

Good coding practice is to never rely on default date formats; always code conversion functions with a date format specifier so that generated dates are never in doubt. And never rely on implicit date conversions as each database configuration is different. The goal is to write portable code that will run on any installation of Oracle the code could encounter (barring functional changes due to version differences). Being lazy with dates can result in broken code and applications that abort seemingly without reason. Don’t let this happen to you.

Be right for the RIGHT reasons.

 

2 Comments »

  1. Hello,

    Please considder using the DATE LITERAL instead, which is universal and works in all Oracle database instances over the world and in most other brands SQL database-servers as well.
    Syntax: DATE ‘YYYY-MM-DD’

    select * from emp where birthdate = date ‘1981-12-25’;

    No conversion function needed with unwanted side effects.

    And there is also a TIME LITERAL

    We use LITERALS for strings and numbers in SQL why not for dates as well?

    Comment by Ronald Hollak — August 30, 2019 @ 02:47

  2. Not every installation uses YYYY-MM-DD format, as illustrated in the post. DATE works with a specific format and when date strings don’t match that format DATE doesn’t work:
    SQL> select date ’01-31-1999′ from dual;
    select date ’01-31-1999′ from dual
    *
    ERROR at line 1:
    ORA-01843: not a valid month

    SQL>

    Date strings must match the format the application or program is willing to accept and not all use YYYY-MM-DD. For those cases where that format is the norm then DATE does work as expected. However one cannot force use of an unsupported format when date string processing is automated, which is the basis for this post.

    I do appreciate the comment; unfortunately it likely won’t apply in many cases.

    Comment by dfitzjarrell — August 30, 2019 @ 08:33


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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.

Create a free website or blog at WordPress.com.

%d bloggers like this: