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