NUMTODSINTERVAL Function in Oracle SQL – PLSQL

The NUMTODSINTERVAL function in Oracle SQL / PLSQL is used to convert any number to an Interval Day to Second Literal.

Syntax for the NUMTODSINTERVAL function in Oracle SQL / PLSQL is:

SELECT NUMTODSINTERVAL(number,expression)
FROM table_name;

  • ‘number’ is the number to be converted to interval
  • ‘expression’ is the unit and can have the following values: ‘DAY’, ‘HOUR’, ‘MINUTE’ OR ‘SECOND’

Example 1:

[sourcecode language=”sql”]
SELECT NUMTODSINTERVAL(250, ‘DAY’)
FROM DUAL; [/sourcecode]

May return “+250 00:00:00.000000”


Example 2:

[sourcecode language=”sql”]
SELECT NUMTODSINTERVAL(2500, ‘HOUR’)
FROM DUAL; [/sourcecode]

May return “+104 04:00:00.000000”


Example 3:

[sourcecode language=”sql”]
SELECT NUMTODSINTERVAL(25000, ‘MINUTE’)
FROM DUAL; [/sourcecode]

May return “+17 08:40:00.000000”


Example 4:

[sourcecode language=”sql”]
SELECT NUMTODSINTERVAL(250000, ‘SECOND’)
FROM DUAL; [/sourcecode]

May return “+02 21:26:40.000000”


Leave a Reply

Your email address will not be published. Required fields are marked *