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:

SYNTAX
{ d ’yyyy-mm-dd’ }
  | mm-dd-yyyy
  | mm/dd/yyyy
  | mm-dd-yy
  | mm/dd/yy
  | yyyy-mm-dd
  | yyyy/mm/dd
  | dd-mon-yyyy
  | dd/mon/yyyy
  | dd-mon-yy
  | dd/mon/yy 

{ 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 format yyyy-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.

EXAMPLES

The following example illustrates using the date literal format with an INSERT statement:

INSERT INTO dtest VALUES ( { d ’1998-05-07’ } ) 

The INSERT and SELECT statements in the following example show some of the supported formats for date literals:

CREATE TABLE T2 (C1 DATE, C2 TIME);
INSERT INTO T2 (C1) VALUES(’5/7/56’);
INSERT INTO T2 (C1) VALUES(’7/MAY/1956’);
INSERT INTO T2 (C1) VALUES(’1956/05/07’);
INSERT INTO T2 (C1) VALUES({d ’1956-05-07’});
INSERT INTO T2 (C1) VALUES(’29-SEP-1952’);
 
SELECT C1 FROM T2;
 
--------------
c1
1956-05-07
1956-05-07
1956-05-07
1956-05-07
1952-09-29 

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:

SYNTAX
{ t ’hh:mi:ss’ } | hh:mi:ss[:mls ] 

{ 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 format hh: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).

EXAMPLES

The following example illustrates using the time literal format with an INSERT statement:

INSERT INTO ttest VALUES ( { t ’23:22:12’ } ) ; 

The INSERT statements in the following example show some of the formats SQL will and will not accept for time literals:

INSERT INTO T2 (C2) VALUES(’3’);
error(-20234): Invalid time string
 
INSERT INTO T2 (C2) VALUES(’8:30’);
error(-20234): Invalid time string
 
INSERT INTO T2 (C2) VALUES(’8:30:1’);
INSERT INTO T2 (C2) VALUES(’8:30:’);
error(-20234): Invalid time string
 
 
INSERT INTO T2 (C2) VALUES(’8:30:00’);
INSERT INTO T2 (C2) VALUES(’8:30:1:1’);
INSERT INTO T2 (C2) VALUES({t’8:30:1:1’}); 

This SELECT statement illustrates which INSERT statements successfully inserted a row:

SELECT C2 FROM T2;c2
 
c2
--
08:30:01
08:30:00
08:30:01
08:30:01 

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:

SYNTAX
{ ts ’yyyy-mm-dd hh:mi:ss’ } | ’date_literal time_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 format yyyy-mm-dd hh:mi:ss.

date_literal

A date literal.

time_literal

A time literal.

EXAMPLES

This example illustrates how to INSERT a time-stamp literal into a column:

INSERT INTO DTEST 
VALUES ( { ts ’1956-05-07 10:41:37’} ) ; 

This example illustrates a time-stamp literal with the ODBC escape clause:

SELECT * FROM DTEST WHERE C1 = {ts ’1985-08-10 05:41:37’} ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095