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

This segment specifies the precision and scale of an INTERVAL data type.

A keyword specifying the largest time unit must be the first keyword, and a keyword specifying the smallest time unit must follow the TO keyword. These can be the same keyword. This segment resembles the syntax of a DATETIME Field Qualifier, but with these exceptions:

• If the largest time unit keyword is YEAR or MONTH, the smallest time unit keyword cannot specify a time unit smaller than MONTH.
• You can specify up to 9-digit precision after the first time unit, unless FRACTION is the first time unit (in which case the limit is 5 digits).

Because year and month are not fixed-length units of time, the database server treats INTERVAL data types that include the YEAR or MONTH keywords in their qualifiers as incompatible with INTERVAL data types whose qualifiers are time units smaller than MONTH. The database server supports no implicit casts between these two categories of INTERVAL data types.

The next two examples show YEAR TO MONTH qualifiers of INTERVAL data types. The first example can hold an interval of up to 999 years and 11 months, because it gives 3 as the precision of the YEAR field. The second example uses the default precision on the YEAR field, so it can hold an interval of up to 9,999 years and 11 months.

YEAR (3) TO MONTH

YEAR TO MONTH

When you want a value to specify only one kind of time unit, the first and last qualifiers are the same. For example, an interval of whole years is qualified as YEAR TO YEAR or YEAR (5) TO YEAR, for an interval of up to 99,999 years.

The following examples show several forms of INTERVAL field qualifiers:

YEAR(5) TO MONTH

DAY (5) TO FRACTION(2)

DAY TO DAY

FRACTION TO FRACTION (4)

For information about how to specify INTERVAL field qualifiers and how to use INTERVAL data in arithmetic and relational operations, see the discussion of the INTERVAL data type in the IBM Informix Guide to SQL: Reference.