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:

SELECT *
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'

or, for TIMESTAMP WITH TIME ZONE:

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

A small tip perhaps but a worthwhile one.