Oracle PL/SQL Programming, 5th Edition

Interval Literals

Interval literals are similar to timestamp literals and are useful when you want to embed interval values as constants within your code. Interval literals take the following form:

INTERVAL 'character_representation' start_element TOend_element

where:

character_representation

Is the character string representation of the interval. See Interval Conversions for a description of how the two interval datatypes are represented in character form.

start_element

Specifies the leading element in the interval.

end_element

Specifies the trailing element in the interval.

Unlike the TO_YMINTERVAL and TO_DSINTERVAL functions, interval literals allow you to specify an interval using any sequence of datetime elements from Table 10-2. There are only two restrictions:

• You must use a consecutive sequence of elements.

• You cannot transition from a month to a day within the same interval.

Following are several valid examples:

DECLARE y2ma INTERVAL YEAR TO MONTH; y2mb INTERVAL YEAR TO MONTH; d2sa INTERVAL DAY TO SECOND; d2sb INTERVAL DAY TO SECOND; BEGIN /* Some YEAR TO MONTH examples */ y2ma := INTERVAL '40-3' YEAR TO MONTH; y2mb := INTERVAL '40' YEAR; /* Some DAY TO SECOND examples */ d2sa := INTERVAL '10 1:02:10.123' DAY TO SECOND; /* Fails in Oracle9i through 11gR2 because of a bug */ --d2sb := INTERVAL '1:02' HOUR TO MINUTE; /* Following are two workarounds for defining intervals, such as HOUR TO MINUTE, that represent only a portion of the DAY TO SECOND range. */ SELECT INTERVAL '1:02' HOUR ...