Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: NUMTODSINTERVAL Jump to:  
Category: >> Oracle PL/SQL >> NUMTODSINTERVAL Bookmark and Share

<< lastnext >>

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
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 339 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?