INTERVAL YEAR TO MONTH

The data type of INTERVAL YEAR TO MONTH stores a time interval in years and months. It format is:

INTERVAL YEAR[(years_precision)]TO MONTH

years_precision, optional, may be an integer from 0 to 9. The default precision is 2. 2 means you can store two digits for the years in your interval. It can store a positive or negative time interval.

An INTERVAL YEAR TO MONTH literal is defined in syntax:

INTERVAL '[+|-][y][-m]' [YEAR[(years_precision)])] [TO MONTH]

where

+, optional, specifies if the time interval is positive, the default is positive.
-, optional, specifies if the time interval is positive.
y, optional, the number of years.
m, optional, the number of months. TO MONTH must be added if you supply years and months.
years_precision, optional, precision for the years, the default is 2.

The following table shows some examples of year-to-month interval literals:

Literal	Description
INTERVAL '1' YEAR	Interval of 1 year.
INTERVAL '1' MONTH	Interval of 1 months.
INTERVAL '14' MONTH	Interval of 14 months (1 year 2 months).
INTERVAL '1-2' YEAR TO MONTH	Interval of 1 year 2 months.
INTERVAL '0-1' YEAR TO MONTH	Interval of 0 years 1 months.
INTERVAL '999' YEAR(3) TO MONTH	Interval of 999 years with a precision of 3 digits.
INTERVAL '-1-2' YEAR TO MONTH	A negative interval of 1 year 2 months.

The following interal literal is invalid:

INTERVAL '1234' YEAR(3)

1234 containing four digits is disallowed by the precision of 3. The precision of 3 means three digits maximum.


CREATE TABLE myTable (
id          INTEGER,
duration    INTERVAL YEAR(3) TO MONTH
); 

INSERT INTO myTable(id, duration)VALUES(1, INTERVAL '1' YEAR); 
INSERT INTO myTable(id, duration)VALUES(2, INTERVAL '1' MONTH); 
INSERT INTO myTable(id, duration)VALUES(3, INTERVAL '99' MONTH); 
INSERT INTO myTable(id, duration)VALUES(4, INTERVAL '1-2' YEAR TO MONTH); 
INSERT INTO myTable(id, duration)VALUES(5, INTERVAL '0-1' YEAR TO MONTH); 
INSERT INTO myTable(id, duration)VALUES(6, INTERVAL '999' YEAR(3)); 

SQL> SELECT * FROM myTable;

        ID DURATION
-----------------------------------------------
         1  +001-00
         2  +000-01
         3  +008-03
         4  +001-02
         5  +000-01
         6  +999-00

6 rows selected.

SQL>

Home »
Oracle »
Data types »

Time_intervals: