DATETIME qual1 TO qual2
The DATETIME
data type stores date and time data with time
units from the year to fractions of a second.
Syntax
DATETIME YEAR TO FRACTION [
( scale ) ]
|
DATETIME YEAR TO SECOND
|
DATETIME YEAR TO MINUTE
|
DATETIME YEAR TO HOUR
|
DATETIME YEAR TO DAY
|
DATETIME YEAR TO MONTH
|
DATETIME YEAR TO YEAR
|
DATETIME MONTH TO FRACTION [
( scale ) ]
|
DATETIME MONTH TO SECOND
|
DATETIME MONTH TO MINUTE
|
DATETIME MONTH TO HOUR
|
DATETIME MONTH TO DAY
|
DATETIME MONTH TO MONTH
|
DATETIME DAY TO FRACTION [
( scale ) ]
|
DATETIME DAY TO SECOND
|
DATETIME DAY TO MINUTE
|
DATETIME DAY TO HOUR
|
DATETIME DAY TO DAY
|
DATETIME HOUR TO FRACTION [
( scale ) ]
|
DATETIME HOUR TO SECOND
|
DATETIME HOUR TO MINUTE
|
DATETIME HOUR TO HOUR
|
DATETIME MINUTE TO FRACTION [
( scale ) ]
|
DATETIME MINUTE TO SECOND
|
DATETIME MINUTE TO MINUTE
|
DATETIME SECOND TO FRACTION [
( scale ) ]
|
DATETIME SECOND TO SECOND
|
DATETIME FRACTION TO FRACTION [
( scale ) ]
- scale defines the scale of the fractional part, it can be 1, 2, 3, 4 or 5.
Usage
The DATETIME
data type stores an instance in time, expressed as a calendar
date and time-of-day.
DATETIME
keyword define the precision of the
DATETIME
type. While many sort of datetime types can be defined with all
possible qualifier combinations, only a limited set of DATETIME
types are
typical used in applications:
DATETIME HOUR TO MINUTE, DATETIME HOUR TO SECOND, DATETIME HOUR TO FRACTION(scale)
: To hold a time value.DATETIME YEAR TO MINUTE, DATETIME YEAR TO SECOND, DATETIME YEAR TO FRACTION(scale)
: To hold a date with time value.
DATETIME YEAR TO DAY
is equivalent to DATE
, consider using
DATE
instead.The year of a DATETIME
can range from 1 to 9999.
When the FRACTION
qualifier is specified without a precision, the precision
defaults to 3.
DATETIME
arithmetic is based on the INTERVAL
data type, and can be combined
with DATE
values:
Left Operand Type | Operator | Right Operand Type | Result Type |
---|---|---|---|
DATETIME |
- |
DATETIME |
INTERVAL |
DATETIME |
- |
DATE |
INTERVAL |
DATETIME |
- |
INTERVAL |
DATETIME |
DATETIME |
+ |
INTERVAL |
DATETIME |
DATETIME
variables are initialized to NULL
in functions,
modules and globals.
CURRENT
operator
provides current system
date/time:DEFINE dt DATETIME YEAR TO SECOND
LET dt = CURRENT
DATETIME
variables can be assigned with datetime literals, by using the
DATETIME() q1 TO q2
notation:DEFINE dt DATETIME YEAR TO SECOND
LET dt = DATETIME(2014-02-21 13:45:34) YEAR TO SECOND
DATETIME
variables can be assigned from string literals, by using the
format YYYY-MM-DD hh:mm:ss.fffff
, or the ISO 8601 format sub-set (with the
T separator between the date and time part, and with optional +/-nn
UTC
indicator or timezone offset):
DEFINE dt DATETIME YEAR TO FRACTION(5)
LET dt = "2012-10-05 11:34:56.99999"
LET dt = "2012-10-05T11:34:56.99999+02:00"
When a DATETIME
value is assigned from a string using the ISO 8601 format, and
the string ends with a "+/-hh:mm
" timezone offset, the value is adjusted to the
local timezone when assigned.
When converting a DATETIME
to a string, the format YYYY-MM-DD
hh:mm:ss.fffff
is used.
Data type conversion can be controlled by catching the runtime exceptions. For more details, see Handling type conversion errors.
DATETIME
value can be converted to a different DATETIME
(or DATE
) with a different precision by using the EXTEND()
operator:MAIN
DEFINE dt1 DATETIME YEAR TO MONTH
DEFINE dt2 DATETIME YEAR TO FRACTION(5)
LET dt1 = CURRENT
LET dt2 = EXTEND(dt1, YEAR TO FRACTION(5))
END MAIN
util.Datetime
class, for example
to convert local datetime to UTC datetime values:IMPORT util
MAIN
DEFINE dt DATETIME YEAR TO FRACTION(5)
LET dt = "2012-10-05 11:34:56.99999"
DISPLAY util.Datetime.toUTC( dt )
END MAIN