Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
NUMTODSINTERVAL
converts n
to an INTERVAL
DAY
TO
SECOND
literal. The argument n
can be any NUMBER
value or an expression that can be implicitly converted to a NUMBER
value. The argument interval_unit
can be of CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
datatype. The value for interval_unit
specifies the unit of n
and must resolve to one of the following string values:
'DAY
'
'HOUR
'
'MINUTE
'
'SECOND
'
interval_unit
is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.
Examples
The following example uses NUMTODSINTERVAL
in a COUNT
analytic function to calculate, for each employee, the number of employees hired by the same manager within the past 100 days from his or her hire date. Please refer to "Analytic Functions" for more information on the syntax of the analytic functions.
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