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 used DATE instead.

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