Release 10.1A: OpenEdge Data Management:
SQL 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

> - All text (names of days, months, ordinal number endings) in all date-format literals must be in the English language. The default date format is American. You can explicitly request another date format by using a format string.

> - Time literals are in the English language only.

> ##### 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 the month as a one– or two–digit number in the range `01 to 31`.

> `mm`

> Specifies the month value as a one– or two–digit number in the range `01 to 12`.

> `mon`

> Specifies the first three 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 how to use the date literal format with an `INSERT` statement:

>     INSERT INTO dtest VALUES ( { d '2004-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 three-digit number in the range `000` to `999`.

> Examples

> The following example illustrates how to use 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'});

> The `SELECT` statement in the following example 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 `t`s. 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.

> `time_literal`

> A time literal.

> Examples

> The following example is example illustrates how to `INSERT` a timestamp literal into a column:

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

> The following example illustrates a timestamp literal with the ODBC escape clause:

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

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