|
|
|
Syntax
date_time_data_type ::
DATE
| TIME
| TIMESTAMP
Arguments
Syntax |
Description |
Literals |
Range |
DATE |
Stores a date value as three parts: year, month, and day. |
{d 'yyyy-mm-dd'} mm-dd-yyyy mm/dd/yyyy yyyy-mm-dd yyyy/mm/dd |
|
TIME |
Stores a time value as three parts: hours, minutes, and seconds. |
{t 'hh:mi:ss'} hh:mi:ss |
|
TIMESTAMP |
Combines the parts of DATE and TIME. |
{ts 'yyyy-mm-dd hh:mi:ss'} mm-dd-yyyy hh:mi:ss mm/dd/yyyy hh:mi:ss yyyy-mm-dd hh:mi:ss yyyy/mm/dd hh:mi:ss
|
|
Date Literals
Date literals specify a day, month, and year. By default, FairCom DB SQL supports any of the following formats, enclosed in single quotation marks ( ' ).
Syntax
date-literal ::
{d 'yyyy-mm-dd'}
| mm-dd-yyyy
| mm/dd/yyyy
| yyyy-mm-dd
| yyyy/mm/dd
| dd-mon-yyyy
| dd/mon/yyyy
Arguments
{d 'yyyy-mm-dd'}
A date literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase d. End the literal with a close brace. For example:
INSERT INTO DTEST VALUES ({d '1994-05-07'})
If you use the ODBC escape clause, you must specify the date using the format yyyy-mm-dd.
dd
The day of month as a 1- or 2-digit number (in the range 01-31).
mm
The month value as a 1- or 2-digit number (in the range 01-12).
mon
The first three characters of the name of the month (in the range ‘JAN’ to ‘DEC’).
yyyy
The year as four-digit number. By default, FairCom DB SQL generates an Invalid date string error if the year is specified as anything other than digits.
Examples
The following FairCom DB SQL examples demonstrate some 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 ( ' ):
Syntax
time-literal ::
{t 'hh:mi:ss'}
| hh:mi:ss
Arguments
{t 'hh:mi:ss'}
A time literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase t. End the literal with a close brace. For example:
INSERT INTO TTEST VALUES ({t '23:22:12'})
If you use the ODBC escape clause, you must specify the time using the format hh:mi:ss.
hh
The hour value as a 1- or 2-digit number (in the range 00 to 23).
mi
The minute value as a 1- or 2-digit number (in the range 00 to 59).
ss
The seconds value as a 1- or 2-digit number (in the range 00 to 59).
Examples
The following FairCom DB SQL examples show some of the formats FairCom DB 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:01');
INSERT INTO T2 (C2) VALUES({t'8:30:01'});
SELECT C2 FROM T2;
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 ( ' ):
Syntax
{ts 'yyyy-mm-dd hh:mi:ss'}
| date-literal time-literal '
Arguments
{ts 'yyyy-mm-dd hh:mi:ss'}
A timestamp literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase ts. End the literal with a close brace. For example:
INSERT INTO DTEST
VALUES ({ts '1956-05-07 10:41:37'})
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.
Example
SELECT * FROM DTEST WHERE C1 = {ts '1956-05-07 10:41:37'}
