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.

The qualifiers following the 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:

Table 1. Datetime Arithmetic operators
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.

The 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.

A 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

Datetime conversion functions are provided in the 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