The syntax for time literals is defined as follows:
time_literal ::=
{
DATE ' year-month-day ' |
TIME ' hour:minute:second ' |
TIMESTAMP' year-month-day hour:minute:second '
}
DATE
Date. The data type of the time literal is DATE.
TIME
Time. The data type of the time literal is TIME(3).
TIMESTAMP
Timestamp. The data type of the time literal is TIMESTAMP(3).
year
Four-digit unsigned integer between 0001 and 9999 indicating the year.
month
Two-digit unsigned integer between 01 and 12 indicating the month.
day
Two-digit unsigned integer between 01 and 31 (corresponding to the month and year) indicating the day.
hour
Two-digit unsigned integer between 00 and 23 indicating the hour.
minute
Two-digit unsigned integer between 00 and 59 indicating the minute.
second
Unsigned fixed-point number between 00.000 and 60.999 that indicates the seconds and fractions of a second. A two-digit specification must be made for the seconds and a three-digit specification for the fractions of a second.
The range of values allows specification of one leap second.
A date specification must observe the rules of the Gregorian calendar even if the date involved is before the introduction of the Gregorian calendar.
In SESAM/SQL, you can use an abbreviated notation without an introductory time keyword if it is clear from the context that you are dealing with a time literal and not an alphanumeric literal.
Examples
To output, from the ORDERS table, all orders which were completed before the specified date.
SELECT * FROM orders WHERE actual < '2013-01-01'
The actual column was defined with the DATE data type during table creation. It is therefore immediately obvious from the left-hand comparison operand that the specified literal is a time literal. The keyword DATE can therefore be omitted on the right-hand side.
Literal in the SELECT list.
SELECT COUNT(*) AS number, '2013-05-01' AS date FROM orders
The derived table contains a row with the number of orders and with the DATE column. The data type results from the specified expression. The data type for the DATE column is therefore CHAR(10).
To avoid possible sources of error, you are recommended to always specify time literals with an introductory time keyword (DATE, TIME, TIMESTAMP).