Tuesday, 7 August 2012

NUMTOYMINTERVAL IN ORACLE

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.


Examples
The following example calculates, for each employee, the total salary of employees hired in the past one year from his or her hire date.
SELECT last_name, hire_date, salary, SUM(salary) 
   OVER (ORDER BY hire_date 
   RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
   FROM employees;

No comments:

Post a Comment