Oracle NUMTODSINTERVAL function
How to convert any number to an interval day to second literal?
The NUMTODSINTERVAL() function is used to convert a number to an INTERVAL DAY TO SECOND literal.
Syntax:
NUMTODSINTERVAL(n, 'interval_unit')
Parameters:
Name | Description |
---|---|
n | Any NUMBER value or an expression that can be implicitly converted to a NUMBER value. |
interval_unit | A 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:
interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. |
Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Pictorial Presentation

Examples: Oracle NUMTODSINTERVAL() function
The following SQL query counts the number of employees hired by the same manager within the past 100 days from his or her hire date.
Sample table: employees
SQL> SELECT manager_id, first_name, hire_date,
2 COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date
3 RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count
4 FROM employees;;
Sample Output:
MANAGER_ID FIRST_NAME HIRE_DATE T_COUNT ---------- -------------------- -------------------- ---------- 100 Lex 13-JAN-2001 00:00:00 1 100 Den 07-DEC-2002 00:00:00 1 100 Payam 01-MAY-2003 00:00:00 1 100 Michael 17-FEB-2004 00:00:00 1 100 Matthew 18-JUL-2004 00:00:00 1 100 John 01-OCT-2004 00:00:00 2 100 Karen 05-JAN-2005 00:00:00 2 100 Alberto 10-MAR-2005 00:00:00 2 100 Adam 10-APR-2005 00:00:00 3 100 Neena 21-SEP-2005 00:00:00 1 100 Shanta 10-OCT-2005 00:00:00 2 . . .
Previous:
NEXT_DAY
Next:
NUMTOYMINTERVAL
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join