DATETIME Field Qualifier:
 
|--+-YEAR--+-TO YEAR----------------------+-----+---------------|
   |       +-TO MONTH---------------------+     |
   |       +-TO DAY-----------------------+     |
   |       +-TO HOUR----------------------+     |
   |       +-TO MINUTE--------------------+     |
   |       +-TO SECOND--------------------+     |
   |       '-TO FRACTION--+-------------+-'     |
   |                      '-(--scale--)-'       |
   +-MONTH--+-TO MONTH------------------------+-+
   |        +-TO DAY--------------------------+ |
   |        +-TO HOUR-------------------------+ |
   |        +-TO MINUTE-----------------------+ |
   |        +-TO SECOND-----------------------+ |
   |        '-TO FRACTION--+-------------+--)-' |
   |                       '-(--scale--)-'      |
   +-DAY--+-TO DAY-----------------------+------+
   |      +-TO HOUR----------------------+      |
   |      +-TO MINUTE--------------------+      |
   |      +-TO SECOND--------------------+      |
   |      '-TO FRACTION--+-------------+-'      |
   |                     '-(--scale--)-'        |
   +-HOUR--+-TO HOUR----------------------+-----+
   |       +-TO MINUTE--------------------+     |
   |       +-TO SECOND--------------------+     |
   |       '-TO FRACTION--+-------------+-'     |
   |                      '-(--scale--)-'       |
   +-MINUTE--+-TO MINUTE--------------------+---+
   |         +-TO SECOND--------------------+   |
   |         '-TO FRACTION--+-------------+-'   |
   |                        '-(--scale--)-'     |
   +-SECOND--+-TO SECOND--------------------+---+
   |         '-TO FRACTION--+-------------+-'   |
   |                        '-(--scale--)-'     |
   '-FRACTION--TO FRACTION--+-------------+-----'
                            '-(--scale--)-'
 

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

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.

For an explanation of the DATETIME Field Qualifier, see the discussion of the DATETIME data type in the IBM Informix Guide to SQL: Reference.

For important differences between the syntax of DATETIME and INTERVAL field qualifiers, see INTERVAL Field Qualifier.