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.
Examples
The following example calculates, for each employee, the number of employees hired by the same manager within the last 100 days from his/her hire date:
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
