Mastering Oracle SQL

INTERVAL Literals

Just as Oracle supports DATE and TIMESTAMP literals, it supports INTERVAL literals too. There are two interval datatypes, and two types of corresponding interval literals: YEAR TO MONTH interval literals and DAY TO SECOND interval literals.

YEAR TO MONTH Interval Literals

A YEAR TO MONTH interval literal represents a time period in terms of years and months. A YEAR TO MONTH interval literal takes on the following form:

INTERVAL 'y [- m]' YEAR[(precision_for_year)] [TO MONTH]

The syntax elements are:

y

An integer value specifying the years.

m

An optional integer value specifying the months. You must include the TO MONTH keywords if you specify a month value.

precision_for_year

Specifies the number of digits to allow for the year. The default is 2. The valid range is from 0 to 9.

The default precision for the year value is 2. If the literal represents a time period greater than 99 years, then we must specify a high-enough precision for the year. The integer value for the month, as well as the MONTH keyword, are optional. If you specify a month value, it must be between 0 and 11. You also need to use the TO MONTH keywords when you specify a month value.

The following example inserts a YEAR TO MONTH interval literal into an INTERVAL YEAR TO MONTH column:

               INSERT INTO EVENT_HISTORY
               VALUES (6001, INTERVAL '5-2' YEAR TO MONTH);

1 row created.

SELECT * FROM EVENT_HISTORY;

  EVENT_ID EVENT_DURATION
---------- ------------------------------------------
      6001 +05-02

The following example ...