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

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

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.

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

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