When you refer to a date-time data type with a literal in an
SQL statement, you must precede the literal with the data type
name and enclose the literal in single quotation marks. You must
provide values for all fields, and values must be within the
valid range for the field.
The following syntax shows the format of date-time literals:
date-time-literal =
--+-> TIME --> ' --> time-body --> ' ------------------------+-->
+-> DATE -+---------+-> ' --> date-body --> ' -------------+
| +-> ANSI -+ |
+-> TIMESTAMP --> ' --+-> date-body time-body -+--> ' --+
+-> date-body : time-body -+
time-body =
---> <hours> : <minutes> : <seconds> ---+
+---------------------<---------------+
++---------------------------+--------->
+-> . <fractional-seconds> -+
date-body =
---> <year> - <month> - <day> -->
NOTE
In the following syntax descriptions, y, d, h, and s stand
for single digits in fields representing years, days, hours,
and seconds, respectively. The letter m stands for 1 digit
of the month number when it follows a y, and 1 digit of the
minutes number when it does not. Fractions of a second are
represented by digits after the decimal point.
The syntax for date-time literals is as follows:
o DATE literals
DATE 'yyyy-mm-dd'
or
DATE 'dd-mmm-yyyy hh:mm:ss.ss'
Examples: DATE ANSI '1993-05-27'
DATE VMS '27-MAY-1993 15:25:00.00'
SQL includes leap year validation for the 29th of February.
o TIME literals
TIME 'h:m:s'
TIME 'h:m:s.s'
Example: TIME '14:23:45.19'
TIME represents 24-hour time.
o TIMESTAMP literals
TIMESTAMP 'y-m-d h:m:s'
or
TIMESTAMP 'y-m-d:h:m:s'
Example: TIMESTAMP '1993-1-4 14:12:01.00'
TIMESTAMP '1993-1-4:14:12:01.00'
There are two formats allowed for the TIMESTAMP literal. The
SQL92 format allows a separating space character between the
date-body and the time-body as shown in the previous example.
The nonstandard format allows a separating colon character
between the date-body and the time-body. For example:
SQL> SET DEFAULT DATE FORMAT 'SQL92';
SQL> --
SQL> -- Create a table and insert several rows using the SQL92 format and
SQL> -- the nonstandard format for the TIMESTAMP literal.
SQL> --
SQL> CREATE TABLE t (a INTEGER, b TIMESTAMP(2)
cont> DEFAULT TIMESTAMP '1995-1-1 12:34:10.01');
SQL> INSERT INTO t (a) VALUE (0);
1 row inserted
SQL> --
SQL> -- Insert a row using the nonstandard format for the TIMESTAMP
SQL> -- literal.
SQL> --
SQL> INSERT INTO t (a,b) VALUE (1, TIMESTAMP '1995-1-1:12:34:10.01');
1 row inserted
SQL> --
SQL> -- Insert a row using the SQL92 format for the TIMESTAMP literal.
SQL> --
SQL> INSERT INTO t (a,b) VALUE (2, TIMESTAMP '1995-1-1 12:34:10.01');
1 row inserted
SQL> --
SQL> -- Select the rows. SQL uses the SQL92 format to display the
SQL> -- TIMESTAMP literal for all selected rows.
SQL> --
SQL> SELECT a, b, CAST (b AS CHAR(30)) FROM t ORDER BY a;
A B
0 1995-01-01 12:34:10.01 1995-01-01 12:34:10.01
1 1995-01-01 12:34:10.01 1995-01-01 12:34:10.01
2 1995-01-01 12:34:10.01 1995-01-01 12:34:10.01
3 rows selected
o INTERVAL literals
INTERVAL '±y-m' YEAR TO MONTH
INTERVAL '±d:h:m:s.s' DAY TO SECOND
Examples: INTERVAL '-1-2' YEAR TO MONTH
INTERVAL '1:4:30:0.0' DAY TO SECOND
INTERVAL '1:10' DAY TO HOUR
INTERVAL '235' MONTH(3)
The following syntax shows the format of interval literals:
interval-literal =
--> INTERVAL --> ' --> <interval-body> --> ' --> interval-qualifier -->
interval-body =
+----++-> years -+---------------+
+> + || +> - + |
+> - ++---------------+> months -+-------------------------------------+>
+> days +--------------+ |
| +> : + | |
+------------+> hours ++-----------+ |
| +> : + | |
+--------------------------+> min ++-----------+ |
| +> : + | |
+--------------------------------------+> sec ++-----------------+
| +> : + |
+--------------------------------------------------+> . frac-sec +
interval-qualifier =
--+-> YEAR ---> prec --+-------------+-----------------+->
| +-> TO MONTH -+ |
+-> MONTH --> prec ----------------------------------+
+-> DAY ----> prec --+-------------------------------+
| +-> TO -+-> HOUR ---------------+
| +-> MINUTE -------------+
| +-> SECOND -> frac -----+
+-> HOUR ---> prec --+-------------------------------+
| +-> TO -+-> MINUTE -------------+
| +-> SECOND -> frac -----+
+-> MINUTE -> prec --+-------------------------------+
| +-> TO SECOND ------> frac -----+
+-> SECOND -> seconds-prec --------------------------+
frac =
--+---------------------------+->
+--> ( <numeric-literal> ) -+
prec =
--+---------------------------+->
+--> ( <numeric-literal> ) -+
seconds-prec =
--+--------------------------------------+-->
+-> ( <numeric-literal-1> ---+ |
+-------------------------+ |
++--------------------------+-> ) --+
+> , <numeric-literal-2> --+
Because intervals can be signed quantities, a leading addition
or subtraction operator can precede the literal to indicate
positive (+) or negative (-) intervals.
o DBKEY string literals
THE DBKEY literal is used primarily by database administrators
who have database keys which were displayed in error messages,
or shown on an RMU/SHOW STATISTICS display and wish to display
the associated row.
The dbkey string literal is prefixed by _DBKEY, or _ROWID to
identify it as a special DBKEY literal. Some examples of valid
DBKEY literals are as follows:
o _DBKEY'23:5628:0'
An Oracle Rdb table dbkey has three parts, a logical area
(in this example 23), a page number (in this example 5628),
and a line number (in this example 0). All three parts must
be specified.
o _ROWID'23:5628:0, 45:345:15'
The DBKEY string literal may include several comma
separated dbkeys if this is used to reference a view
table. Each DBKEY references a row from the view made up
of component rows from a table.
The ROWID keyword is a synonym for DBKEY.
Leading and trailing spaces are ignored, however, spaces may
not be embedded within the numeric values in the DBKEY.
Errors will be reported if the DBKEY is for a different table,
is incorrectly formatted, or does not reference a row. The
reported errors are shown in the following example. A question
mark is placed within the string to highlight the syntax
error.
SQL> select * from employees where dbkey = _dbkey'1,2,3';
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-DBKFORMAT, database key format incorrect "1,?2,3" - unexpected
character
SQL> select * from employees where dbkey = _dbkey'-1:+2:0';
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-DBKFORMAT, database key format incorrect "-1:+?2:0" - unexpected
character
SQL> select * from employees where dbkey = _dbkey'23:1:1';
%RDB-E-NO_RECORD, access by dbkey failed because dbkey is no longer associated
with a record
-RDMS-F-INVDBK, 23:1:1 is not a valid dbkey