NUMTOYMINTERVAL

Syntax

Description of the illustration numtoyminterval.gif

Purpose

NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

• 'YEAR'

• 'MONTH'

interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.

Examples

The following example uses NUMTOYMINTERVAL in a SUM analytic function to calculate, for each employee, the total salary of employees hired in the past one year from his or her hire date. Please refer to "Analytic Functions" for more information on the syntax of the analytic functions.

SELECT last_name, hire_date, salary, SUM(salary) 
   OVER (ORDER BY hire_date 
   RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
   FROM employees;

LAST_NAME                 HIRE_DATE     SALARY      T_SAL
------------------------- --------- ---------- ----------
King                      17-JUN-87      24000      24000
Whalen                    17-SEP-87       4400      28400
Kochhar                   21-SEP-89      17000      17000
. . .
Markle                    08-MAR-00       2200     112400
Ande                      24-MAR-00       6400     106500
Banda                     21-APR-00       6200     109400
Kumar                     21-APR-00       6100     109400