Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

NUMTOYMINTERVAL

Syntax

numtoyminterval::=

Text description of functions176.gif follows
Text description of numtoyminterval


Purpose

NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number. char_expr can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for char_expr specifies the unit of n, and must resolve to one of the following string values:

char_expr is case insensitive. Leading and trailing values within the parentheses are ignored. By default, 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/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;

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
.
.
.
Markle                    08-MAR-00       2200     112400
Ande                      24-MAR-00       6400     106500
Banda                     21-APR-00       6200     109400
Kumar                     21-APR-00       6100     109400