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

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

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.

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

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 }}