Oracle NUMTODSINTERVAL function

Last update on May 30 2022 11:00:11 (UTC/GMT +8 hours)

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: 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.

Name

Description

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:

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.

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