Progress
SQL-92
Guide and Reference
Date-time Literals
SQL supports special formats for literals to be used in conjunction with date-time data types. Basic predicates and the VALUES clause of INSERT statements can specify date literals directly for comparison and insertion into tables. In other cases, you need to convert date literals to the appropriate date-time data type with the CAST, CONVERT, or TO_DATE scalar functions.
Enclose date-time literals in single quotation marks ( ’ ’ ).
NOTES
Date Literals
A date literal specifies a day, month, and year using any of the following formats, enclosed in single quotation marks ( ’ ’ ).
This is the syntax for a date_literal:
{ d ’yyyy-mm-dd’ }
A date literal enclosed in an escape clause is compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase d. End the literal with a close brace.
NOTE: If you use the ODBC escape clause, you must specify the date using the formatyyyy-mm-dd
.dd
Specifies the day of month as a one or two digit number in the range 01-31.
mm
Specifies the month value as a one or two digit number in the range 01-12.
mon
Specifies the first 3 characters of the name of the month in the range ’JAN’ to ’DEC’.
yy
Specifies the last two digits of the year.
yyyy
Specifies the year as a four digit number.
EXAMPLESThe following example illustrates using the date literal format with an INSERT statement:
The INSERT and SELECT statements in the following example show some of the supported formats for date literals:
Time Literals
Time literals specify an hour, minute, second, and millisecond, using the following format, enclosed in single quotation marks ( ’ ’ ).
This is the syntax for a time_literal:
{ t ’hh:mi:ss’ }
A time literal enclosed in an escape clause is compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase t. End the literal with a close brace ( } ).
NOTE: If you use the ODBC escape clause, you must specify the time using the formathh
:
mi
:
ss
.
hh
Specifies the hour value as a two-digit number (in the range 00 to 23).
mi
Specifies the minute value as a two-digit number (in the range 00 to 59).
ss
Specifies the seconds value as a two-digit number (in the range 00 to 59).
mls
Specifies the milliseconds value as a two-digit number (in the range 000 to 999).
EXAMPLESThe following example illustrates using the time literal format with an INSERT statement:
The INSERT statements in the following example show some of the formats SQL will and will not accept for time literals:
This SELECT statement illustrates which INSERT statements successfully inserted a row:
Timestamp Literals
Timestamp literals specify a date and a time separated by a space, enclosed in single quotation marks ( ’ ’ ).
This is the syntax for a timestamp_literal:
{ ts ’yyyy-mm-dd hh:mi:ss’ }
A timestamp literal enclosed in an escape clause is compatible with ODBC. Precede the literal string with an open brace (
{
) and a lowercase ts. End the literal with a close brace (}
). Note that braces are part of the syntax.NOTE: If you use the ODBC escape clause, you must specify the timestamp using the formatyyyy-mm-dd hh:mi:ss
.date_literal
A date literal.
time_literal
A time literal.
EXAMPLESThis example illustrates how to INSERT a time-stamp literal into a column:
This example illustrates a time-stamp literal with the ODBC escape clause:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |