DATE Literals

I have previously noted problems that might occur with implicit type conversion involving dates. Ad-hoc scripts are a major source of the problem. Rather than putting TO_DATE functions around date literals people tend to take the lazier approach and just specify the string literal. I’m not sure in which version it was introduced (somewhere around 11 perhaps) but Oracle has a formal wany of specifying a date literal and that format is:

DATE 'yyyy-mm-dd'

Date literals do not permit a time component to be specified. In case this needs further elaboration, an example using the EMP table would be:

FROM   emp
WHERE  hiredate >= DATE '1981-07-01'

to find all those employees hired on or after 1st July 1981. This is a little bit easier than TO_DATE where you will need to specify the format parameter as well as the date string itself.

Similarly, TIMESTAMP literals may be specified using:

TIMESTAMP 'yyyy-mm-dd hh2:mi:ss.ff'


TIMESTAMP 'yyyy-mm-dd hh2:mi:ss.ff tzh:tzm'

A small tip perhaps but a worthwhile one.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s