NUMTODSINTERVAL

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

NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND 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:

• 'DAY'

• 'HOUR'

• 'MINUTE'

• 'SECOND'

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 number of employees hired, by the same manager, within the last 100 days from his/her hiredate:

SELECT mgr, ename, hiredate, 
   COUNT(*) OVER (PARTITION BY mgr ORDER BY hiredate 
   RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count 
   FROM emp;

       MGR ENAME      HIREDATE     T_COUNT
---------- ---------- --------- ----------
      7566 FORD       03-DEC-81          1
      7566 SCOTT      19-APR-87          1
      7698 ALLEN      20-FEB-81          1
      7698 WARD       22-FEB-81          2
      7698 TURNER     08-SEP-81          1
      7698 MARTIN     28-SEP-81          2
      7698 JAMES      03-DEC-81          3
      7782 MILLER     23-JAN-82          1
      7788 ADAMS      23-MAY-87          1
      7839 JONES      02-APR-81          1
      7839 BLAKE      01-MAY-81          2
      7839 CLARK      09-JUN-81          3
      7902 SMITH      17-DEC-80          1
           KING       17-NOV-81          1