Quick Search:
 
 Oracle PL/SQL: NUMTOYMINTERVAL Jump to:  
Category: >> Oracle PL/SQL >> NUMTOYMINTERVAL  

<< lastnext >>

Snippet Name: NUMTOYMINTERVAL

Description: NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH 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:

'YEAR'
'MONTH'

'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
» NUMTODSINTERVAL
» HEXTORAW
» DECOMPOSE
» CONVERT

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009

NUMTOYMINTERVAL(n, <interval_unit>)
 
-- The following example uses NUMTOYMINTERVAL in a SUM 
-- analytic function to calculate, for each employee, the 
-- total salary of employees hired in the past one year 
-- from his or her hire date. 
 
conn hr/hr
 
SELECT last_name, hire_date, salary, SUM(salary) 
   OVER (ORDER BY hire_date 
   RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
   FROM employees;
 
LAST_NAME                 HIRE_DATE     SALARY      T_SAL
------------------------- --------- ---------- ----------
King                      17-JUN-87      24000      24000
Whalen                    17-SEP-87       4400      28400
Kochhar                   21-SEP-89      17000      17000
 


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org