timestamp
An expression that specifies a timestamp, date, or date and time representation. Specify timestamp as one of these data type classes:
Numeric Timestamps
The $HOROLOG and $ZTIMESTAMP special variables specify the current date as a numeric character string. DATE casts such strings to 0, which represents the date December 31, 1840.
SELECT
DATE($HOROLOG), -- Returns 0 (12/31/1840)
DATE($ZTIMESTAMP) -- Returns 0 (12/31/1840)
To interpret $HOROLOG or $ZTIMESTAMP as the current date, you must force numeric interpretation by prefixing the date with a plus (+) sign.
SELECT
DATE(+$HOROLOG), -- Returns the current date
DATE(+$ZTIMESTAMP) -- Returns the current date
String Timestamps
String timestamps converted to the DATE format, must be compatible with the %Library.TimeStampOpens in a new tab data type. The data type stores strings in the ODBC date format, and the DATE function validates input strings against this format. If the string passes validation, DATE returns the corresponding date. If it fails validation, DATE returns 0, which corresponds to the date December 31, 1840. An empty string ('') also returns 0. A NULL argument returns NULL.
DATE performs these validation checks:
-
The string corresponds to ODBC format, where fff is fractional seconds:
-
The string contains at least a full date: yyyy-mm-dd. The time portion is optional. DATE validates any specified times but does not return them. Any part of the time can be included. For example: yyyy-mm-dd hh:
-
The string does not contain any invalid format characters or trailing characters. Leading zeros can be omitted or included.
-
Each numeric element of the string, including the optional time portion, contains a valid value given the range for that element. For example:
-
Month values are from 1 to 12.
-
Day values do not exceed the number of days for the specified month (includes leap year days).
-
Dates are within the range of 0001-01-01 to 9999-12-31.