w3resource

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

Pictorial Presentation of Oracle NUMTODSINTERVAL function

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



Share this Tutorial / Exercise on : Facebook and Twitter