NUMTOYMINTERVAL

Syntax

numtoyminterval::=

Text description of numtoyminterval

Purpose

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

• 'YEAR'
• 'MONTH'

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

Examples

The following example calculates, for each employee, the total salary of employees hired in the past one year from his/her hire date.

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