DATETIME Field Qualifier

Use a DATETIME Field Qualifier to specify the largest and smallest unit of time in a DATETIME column or value. Use this segment whenever you see a reference to a DATETIME Field Qualifier in a syntax diagram.

Syntax

DATETIME Field Qualifier

1  YEAR
2.1 TO YEAR
2.1 TO MONTH
2.1 TO DAY
2.1 TO HOUR
2.1 TO MINUTE
2.1 TO SECOND
2.1  TO FRACTION?  ( scale )
1  MONTH
2.1 TO MONTH
2.1 TO DAY
2.1 TO HOUR
2.1 TO MINUTE
2.1 TO SECOND
2.1  TO FRACTION?  ( scale ) )
1  DAY
2.1 TO DAY
2.1 TO HOUR
2.1 TO MINUTE
2.1 TO SECOND
2.1  TO FRACTION?  ( scale )
1  HOUR
2.1 TO HOUR
2.1 TO MINUTE
2.1 TO SECOND
2.1  TO FRACTION?  ( scale )
1  MINUTE
2.1 TO MINUTE
2.1 TO SECOND
2.1  TO FRACTION?  ( scale )
1  SECOND
2.1 TO SECOND
2.1  TO FRACTION?  ( scale )
1  FRACTION  TO FRACTION?  ( scale )

Element Description Restrictions Syntax
scale Fraction of a second. Default is 3. Integer (1 < scale < 5) Literal Number

Usage

This segment specifies the precision and scale of a DATETIME data type.

Specify, as the first keyword, the largest time unit that the DATETIME column will store. After the keyword TO, specify the smallest unit as the last keyword. These can be the same keyword. If they are different, the qualifier implies that any intermediate time units between the first and last are also recorded by the DATETIME data type.

The keywords can specify the following time units for the DATETIME column.

Unit of Time
Description
YEAR
Specifies a year, in the range from A.D. 1 to 9999
MONTH
Specifies a month, in the range from 1 (January) to 12 (December)
DAY
Specifies a day, in the range from 1 to 28, 29, 30, or 31 (depending on the specific month)
HOUR
Specifies an hour, in the range from 0 (midnight) to 23
MINUTE
Specifies a minute, in the range from 0 to 59
SECOND
Specifies a second, in the range from 0 to 59
FRACTION
Specifies a fraction of a second, with up to five decimal places

The default scale is three digits (thousandth of a second).

Unlike INTERVAL qualifiers, DATETIME qualifiers cannot specify nondefault precision (except for FRACTION, when FRACTION is the smallest unit in the qualifier). Some examples of DATETIME qualifiers follow: 
YEAR TO MINUTE             MONTH TO MONTH
DAY TO FRACTION(4)         MONTH TO DAY

On some platforms, the system clock cannot support precision greater than FRACTION(3).

An error results if the first keyword represents a smaller time unit than the last, or if you use the plural form of a keyword (such as MINUTES).

Operations on DATETIME values that do not include YEAR in their qualifier use values from the system clock-calendar to supply any additional precision. If the first term in the qualifier is DAY, and the current month has fewer than 31 days, unexpected results can occur.