NUMTOYMINTERVAL

Note: This function is restricted to use with analytic functions. It accepts only numbers as arguments, and returns interval literals. See "Analytic Functions" and "Interval".

Syntax

Purpose

NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number. 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.

Example

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

SELECT ename, hiredate, sal, SUM(sal) OVER (ORDER BY hiredate 
   RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
   FROM emp;

ENAME      HIREDATE         SAL      T_SAL
---------- --------- ---------- ----------
SMITH      17-DEC-80        800        800
ALLEN      20-FEB-81       1600       2400
WARD       22-FEB-81       1250       3650
JONES      02-APR-81       2975       6625
BLAKE      01-MAY-81       2850       9475
CLARK      09-JUN-81       2450      11925
TURNER     08-SEP-81       1500      13425
MARTIN     28-SEP-81       1250      14675
KING       17-NOV-81       5000      19675
JAMES      03-DEC-81        950      23625
FORD       03-DEC-81       3000      23625
MILLER     23-JAN-82       1300      24125
SCOTT      19-APR-87       3000       3000
ADAMS      23-MAY-87       1100       4100