Oracle PL/SQL Programming, 5th Edition

Date and Timestamp Literals

Date and timestamp literals, as well as the interval literals that appear later in this chapter, are part of the ISO SQL standard and have been supported since Oracle9i Database. They represent yet another option for you to use in getting values into datetime variables. A date literal consists of the keyword DATE followed by a date (and only a date) value in the following format:

DATE 'YYYY-MM-DD'

A timestamp literal consists of the keyword TIMESTAMP followed by a datetime value in a very specific format:

TIMESTAMP 'YYYY-MM-DD HH:MI:SS[.FFFFFFFFF] [{+|-}HH:MI]'

The FFFFFFFFF represents fractional seconds and is optional. If you specify fractional seconds, you may use anywhere from one to nine digits. The time zone displacement (+HH:MI) is optional and may use either a plus or a minus sign as necessary. The hours are always with respect to a 24-hour clock.

Note

If you omit the time zone displacement in a timestamp literal, the time zone will default to the session time zone.

The following PL/SQL block shows several valid date and timestamp literals:

DECLARE ts1 TIMESTAMP WITH TIME ZONE; ts2 TIMESTAMP WITH TIME ZONE; ts3 TIMESTAMP WITH TIME ZONE; ts4 TIMESTAMP WITH TIME ZONE; ts5 DATE; BEGIN --Two digits for fractional seconds ts1 := TIMESTAMP '2002-02-19 11:52:00.00 −05:00'; --Nine digits for fractional seconds, 24-hour clock, 14:00 = 2:00 PM ts2 := TIMESTAMP '2002-02-19 14:00:00.000000000 −5:00'; --No fractional seconds at all ts3 := TIMESTAMP '2002-02-19 13:52:00 ...