Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

About the Date Literal in Oracle Database

DZone's Guide to

About the Date Literal in Oracle Database

Many developers are not aware of the date literal feature of both SQL and PL/SQL, so I figured I should give you all a bit more detail on the topic.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

I offered up a past blog post on Twitter a few months ago:

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 that many developers are not aware of the date literal feature of both SQL and PL/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:

Date literal:

DATE '2017-08-26' 

 Timestamp literal:

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

Interval literal:

INTERVAL '100-4' YEAR(3) TO MONTH 

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.

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,oracle ,date literal ,tutorial ,sql ,plsql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}