O'Reilly logo

Mastering Oracle SQL by Alan Beaulieu, Sanjay Mishra

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required