Previous
Previous
 
Next
Next

YMINTERVAL Data Type

YMINTERVAL corresponds to the SQL INTERVAL YEAR TO MONTH data type. It stores a period of time using the YEAR and MONTH datetime fields. This data type is useful for representing the difference between two datetime values when only the year and month values are significant.

Specify YMINTERVAL interval literals using the following syntax.

INTERVAL 'integer [- integer ]' YEAR|MONTH [(precision) ] [TO YEAR | MONTH ]

where

If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL '0-1' MONTH TO YEAR is not valid.

The following YMINTERVAL literal indicates an interval of 123 years, 2 months:

INTERVAL '123-2' YEAR(3) TO MONTH

Examples of the other forms of the literal follow, including some abbreviated versions:

Form of Interval Literal Interpretation
INTERVAL '123-2' YEAR(3) TO MONTH An interval of 123 years, 2 months. You must specify the leading field precision if it is greater than the default of 2 digits.
INTERVAL '123' YEAR(3) An interval of 123 years 0 months.
INTERVAL '300' MONTH(3) An interval of 300 months.
INTERVAL '4' YEAR Maps to INTERVAL '4-0' YEAR TO MONTH and indicates 4 years.
INTERVAL '50' MONTH Maps to INTERVAL '4-2' YEAR TO MONTH and indicates 50 months or 4 years 2 months.
INTERVAL '123' YEAR Returns an error, because the default precision is 2, and '123' has 3 digits.

You can add or subtract one INTERVAL YEAR TO MONTH literal to or from another to yield another INTERVAL YEAR TO MONTH literal. For example:

INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = 
INTERVAL '6-11' YEAR TO MONTH