Interval Conversions

An interval is composed of one or more datetime elements. For example, you might choose to express an interval in terms of years and months, or you might choose to speak in terms of hours and minutes. Table 10-2 lists the standard names for each of the datetime elements used to express intervals. These are the names you must use in conjunction with the conversion functions and expressions described in the subsections that follow. The names are not case-sensitive when used with the interval conversion functions. For example, YEAR, Year, and year are all equivalent.

Table 10-2. Interval element names




Some number of years, ranging from 1 through 999,999,999


Some number of months, ranging from 0 through 11


Some number of days, ranging from 0 to 999,999,999


Some number of hours, ranging from 0 through 23


Some number of minutes, ranging from 0 through 59


Some number of seconds, ranging from 0 through 59.999999999

Converting from Numbers to Intervals

The NUMTOYMINTERVAL and NUMTODSINTERVAL functions allow you to convert a single numeric value to one of the interval datatypes. You do this by associating your numeric value with one of the interval elements listed in Table 10-2.

The function NUMTOYMINTERVAL (pronounced “num to Y M interval”) converts a numeric value to an interval of type INTERVAL YEAR TO MONTH. The function NUMTODSINTERVAL (pronounced “num to D S interval”) likewise converts a numeric value to an interval of type INTERVAL ...

Get Oracle PL/SQL Programming, 5th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.