DATE Literals
For the DATE datatype there is only one form of literal, the ANSI date literal, which uses the format "YYYY- MM-DD". Here are some examples using legal variations of the format (along with a little computing trivia).
DATE '-2400-01-01' -- 2400 BC: the abacus invented in Babylonia
DATE '-500-1-1' -- 500 BC: first known use of zero by mathematicians in ancient
-- India
DATE '+0820-01-1' -- 820 AD: Persian mathematician Al-Khwarizmi described rudiments
-- of modern algebra (the term "algorithm" comes from
-- al-Khwarizmi's Latinized name "Algoritmi")
DATE '1947-12-16' -- the transistor invented in USA by Shockley, Bardeen and Brattain
There is no DATE literal format that allows specification of both a date and a time. Instead, an expression returning a DATE datatype must be used. For example, the following expressions all return a DATE value of 2:30 PM on June 15, 2007.
DATE '2007-06-15' + 14.5/24 CAST( TIMESTAMP '2007-06-15 14:30' AS DATE ) TO_DATE( '2007-06-15 14:30', 'YYYY-MM-DD HH24:MI' ) TO_DATE( '2007-06-15 02:30 PM', 'YYYY-MM-DD HH12:MI AM' )
TIMESTAMP Literals
The following examples show various literal formats for specifying TIMESTAMP values.
TIMESTAMP '-2400-01-01 12:34:56' TIMESTAMP '-500-1-1 00:00:00' TIMESTAMP '+0820-01-1 11:23:58' TIMESTAMP '1947-12-16 01:10:11' TIMESTAMP '2007-06-15 01:02:03' TIMESTAMP '2007-06-15 1:2:3' TIMESTAMP '2007-06-15 14:30:00.1' TIMESTAMP '2007-06-15 14:30:00.12' TIMESTAMP '2007-06-15 14:30:00.123' TIMESTAMP '2007-06-15 14:30:00.1234' TIMESTAMP '2007-06-15 14:30:00.12345' TIMESTAMP '2007-06-15 14:30:00.123456' TIMESTAMP '2007-06-15 14:30:00.1234567' TIMESTAMP '2007-06-15 14:30:00.12345678' TIMESTAMP '2007-06-15 14:30:00.123456789'
TIMESTAMP WITH TIME ZONE Literals
The following examples show the various literal formats for specifying TIMESTAMP WITH TIME ZONE values. Each sample is equivalent to 2:30 PM on June 15, 2007 UTC.
TIMESTAMP '2007-06-15 14:30:00 +00:00' TIMESTAMP '2007-06-15 09:30:00 -05:00' TIMESTAMP '2007-06-15 10:30:00 Canada/Eastern' TIMESTAMP '2007-06-15 10:30:00 Canada/Eastern EDT'
(Allowable timezone names and abbreviations for your system can be found by querying V$TIMEZONE_NAMES. See Oracle® Database Globalization Support Guide - Choosing a Time Zone File for more details.)
Gotchas
Note the formats defined in the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT initialization/session parameters have no effect on the format used for date and timestamp literals. For example, this will not work.
alter session set NLS_DATE_FORMAT = 'YYYY/MM/DD' ;
select date '2007/06/15' from dual ;
select date '2007/06/15' from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT only affect the appearance of selected datetimes and act as the default format masks for the TO_CHAR and TO_DATE functions.