About the Date Literal in Oracle Database

I offered up a past blog post on Twitter today:

And I saw this reply:
I am unfamiliar with this magical expression: DATE '2016-10-31'. How is this being resolved when it doesn't match the NLS date format?
Which reminded me than many developers are not aware of the date Literal feature of both SQL andPL/SQL. So I figured I should give you all a bit more detail on the topic.

So you are likely familiar with string literals, like 'ABC' and q'[don't need two single quotes]'.  And who isn't aware of using pretty much all the time number literals, like 123 and 2e7?

Relatively few developers know that you can have a date literal, too (and timestamp literal and timestamp with local timezone literal and interval literals).

Literals of these types generally have the form:

TYPE string-literal

where TYPE is the name of the datatype and string-literal is, well, you get the idea. :-)

Here are some examples:

1. Date literal

DATE '2017-08-26'

2. Timestamp literal

TIMESTAMP '2017-08-26 09:26:50.124'

2. Interval literal


So why does the date literal "work" even if it doesn't match the default date format for my session?

Because the date literal is defined as part of the ANSI SQL standard. It contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Sorry, that's just how it is.

Now that you know about the date literal, you can test your knowledge with a Dev Gym quiz (just search for "date literal").

You can also play around with it easily using this LiveSQL script.


  1. I believe there is a typo in your Live SQL script:

    l_holiday DATE := TO_DATE ('2016-01-01', 'YYYY-MM-DD;);

    should probably be

    l_holiday DATE := TO_DATE ('2016-01-01', 'YYYY-MM-DD');

    changing the ";" to a "'" after the DD.


Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts