Oracle Date Format Errors and Tips

What is a date literal

A date literal is a string that represents a date. Another way to say this is that a date literal is a string that Oracle needs to convert to a date to work with. A simple example of a date literal in SQL is:

select * from mytable where mydate > '11-JAN-1999';

-- Note that column mydate is of DATE datatype!

What's the problem with the above query?

The problem with the above query is that Oracle has to convert the date literal ('11-JAN-1999') using the default date format currently set for the user's session. In Oracle this default is generally 'DD-MON-RR' and so if the default date format hasn't been changed the above query will work without a problem. However if the default date format HAS been changed than we're going to get one of the following errors:

ORA-01843: not a valid month

ORA-01861: literal does not match format string

ORA-01839: date not valid for month specified

Using a string literal as a date in Oracle as shown in the above query is an example of implicit date conversion. Oracle will use the currently set default date format to convert the string to a date. This works fine as long as the currently set date format matches the format of our string and fails hard if it does not (or doesn't fail but gives a wrong result, which is even worse!)

This problem can be compounded if the date literal is used in a stored procedure, trigger or a view since the user's session may have a different default date format than the developer.

Explicit conversion with to_date

Here's how to correctly use the to_date function to solve the issue mentioned above:

select * from mytable where mydate > to_date('11-JAN-1999', 'DD-MON- YYYY');

This query will work regardless of the current sessions default date format. This makes the query portable (anyone can run it without errors) and safe.

Best practice for date literals

Anytime you use a string literal as a date in Oracle you absolutely should use to_date. There is no performance penalty for doing this as Oracle has to do it internally if to_date is omitted.

Some side notes:

Note setting NLS_DATE_FORMAT can be done as an environment variable (reg var?) As of Oracle 10 you can use SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') You can use DATE '1999-01-01' but it can't contain a time portion.