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:
-- Note that column mydate is of DATE datatype!
select * from mytable where mydate > '11-JAN-1999';

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'. 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, then 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.

The solution? Explicit date conversion using the TO_DATE function.

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 session's 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

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.