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