Section 10.5.  Date and Timestamp Literals

10.5. Date and Timestamp Literals Date and timestamp literals are part of the ISO SQL standard and are supported in Oracle 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. 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 date and 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 (e.g., a constant) in your code. Oracle allows the use of time zone region names in timestamp literalsfor example: TIMESTAMP '2002-02-19 13:52:00 EST'. However, this functionality goes above and beyond the SQL standard.