NUMTODSINTERVAL

Syntax

numtodsinterval::=

Text description of numtodsinterval

Purpose

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

• '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 manager_id, last_name, hire_date, 
   COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date 
   RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count 
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE    T_COUNT
---------- ------------------------- --------- ----------
       100 Kochhar                   21-SEP-89          1
       100 De Haan                   13-JAN-93          1
       100 Raphaely                  07-DEC-94          1
       100 Kaufling                  01-MAY-95          1
       100 Hartstein                 17-FEB-96          1
.
.
.
       149 Grant                     24-MAY-99          1
       149 Johnson                   04-JAN-00          1
       201 Goyal                     17-AUG-97          1
       205 Gietz                     07-JUN-94          1
           King                      17-JUN-87          1