Date and timestamp literals

Date and timestamp literals are part of the ANSI SQL standard, and are newly supported in Oracle9i. 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.

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 a TIMESTAMP WITH TIME ZONE; b TIMESTAMP WITH TIME ZONE; c TIMESTAMP WITH TIME ZONE; d TIMESTAMP WITH TIME ZONE; e DATE;BEGIN --Two digits for fractional seconds a := TIMESTAMP '2002-02-19 11:52:00.00 -05:00'; --Nine digits for fractional seconds, 24-hour clock, 14:00 = 2:00 PM b := TIMESTAMP '2002-02-19 14:00:00.000000000 -5:00'; --No fractional seconds at all c := TIMESTAMP '2002-02-19 13:52:00 -5:00'; --No time zone, defaults to session time zone d := TIMESTAMP '2002-02-19 13:52:00'; --A date literal e := DATE '2002-02-19';END;

The format for timestamp literals is prescribed by the ANSI/ISO standards, and cannot be changed by you or by the DBA. Thus, it's safe to use timestamp literals whenever you need to embed a specific datetime value in your code.

When using a timestamp literal, you may also specify the time zone using a time zone region name. For example, the following block shows that 10:52 -8:00 (Pacific Standard Time in the U.S.) is equivalent to 13:52 U.S. Eastern Standard Time:

DECLARE a TIMESTAMP WITH TIME ZONE; b TIMESTAMP WITH TIME ZONE;BEGIN a := TIMESTAMP '2002-02-19 10:52:00 -8:00'; b := TIMESTAMP '2002-02-19 13:52:00 EST'; IF a = b THEN dbms_output.put_line('a = b'); END IF;END;

The output is:

a = b

Our use of EST in this example may be confusing, but it serves to illustrate an important point: EST is listed in V$TIMEZONE_NAMES as both a region name (the TZNAME column) and an abbreviation (the TZABBREV column). As a region name, EST may be used to specify time zone information in a timestamp literal. In this example, it's clear that EST is being interpreted as U.S. Eastern Standard Time.