Section 10.7.  Interval Literals

10.7. 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 TO end_element where: character_representation Is the character string representation of the interval. See the section "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 A INTERVAL YEAR TO MONTH; B INTERVAL YEAR TO MONTH; C INTERVAL DAY TO SECOND; D INTERVAL DAY TO SECOND; BEGIN /* Some YEAR TO MONTH examples */ A := INTERVAL '40-3' YEAR TO MONTH; B := INTERVAL '40' YEAR; /* Some DAY TO SECOND examples */ C := INTERVAL '10 1:02:10.123' DAY TO SECOND;   /* Fails in Oracle9i, Release 1 because of a bug */ --D := 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 TO MINUTE INTO D FROM dual; D := INTERVAL '1' HOUR + INTERVAL '02' MINUTE; END; In Oracle9i Database Releases 1 and 2, and, surprisingly, also in Oracle Database 10g Release 1, expressions such as INTERVAL '1:02' HOUR TO MINUTE that don't specify a value for each possible element will work from a SQL statement but not from a PL/SQL statement. Furthermore, you'll get an error about using the keyword BULK in the wrong context. This is a bug that we hope to see fixed in a future release. One very convenient thing that Oracle will do for you is to normalize interval values. In the following example, 72 hours and 15 minutes is normalized to 3 days, 0 hours, and 15 minutes: DECLARE A INTERVAL DAY TO SECOND; BEGIN SELECT INTERVAL '72:15' HOUR TO MINUTE INTO A FROM DUAL; DBMS_OUTPUT.PUT_LINE(A); END; The output is: +03 00:15:00.000000 Oracle will normalize only the high-end value (hours in this example) of an interval literal. An attempt to specify an interval of 72:75 (72 hours and 75 minutes) results in an error.