Snippet Name: NUMTODSINTERVAL
Description: 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.
Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» UNISTR
» TRANSLATE
» TO_YMINTERVAL
» TO_TIMESTAMP_TZ
» TO_TIMESTAMP
» TO_SINGLE_BYTE
» TO_NUMBER
» TO_NCLOB
» TO_NCHAR
» TO_MULTI_BYTE
» TO_LOB
» TO_DSINTERVAL
» TO_DATE
» TO_CLOB
» TO_CHAR
» TO_BINARY_FLOAT
» TO_BINARY_DOUBLE
» TIMESTAMP_TO_SCN
» SCN_TO_TIMESTAMP
» ROWIDTONCHAR
» ROWIDTOCHAR
» REFTOHEX
» RAWTONHEX
» RAWTOHEX
» NUMTOYMINTERVAL
» HEXTORAW
» DECOMPOSE
» CONVERT
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009
|
NUMTODSINTERVAL(n, <interval_unit>)
-- 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.
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
|
