In Oracle Database, the NUMTODSINTERVAL() function converts a number to an INTERVAL DAY TO SECOND literal.
Syntax
The syntax goes like this:
NUMTODSINTERVAL(n, 'interval_unit')The n argument is the number for which to convert.
The value for interval_unit specifies the unit of n and must resolve to one of the following string values:
- ‘
DAY‘ - ‘
HOUR‘ - ‘
MINUTE‘ - ‘
SECOND‘
The interval_unit argument is case insensitive.
Example
Here’s an example to demonstrate:
SELECT NUMTODSINTERVAL(1, 'SECOND')
FROM DUAL;Result:
+00 00:00:01.000000
Here’s another one that uses a minute:
SELECT NUMTODSINTERVAL(1, 'MINUTE')
FROM DUAL;Result:
+00 00:01:00.000000
Fractional Seconds
By default, the precision of the return is 9. Here’s one that specifies fractional seconds in the first argument:
SELECT NUMTODSINTERVAL(10.123456789, 'SECOND')
FROM DUAL;Result:
+00 00:00:10.123456789
Here’s what happens if we change it to another interval unit:
SELECT NUMTODSINTERVAL(10.123456789, 'DAY')
FROM DUAL;Result:
+10 02:57:46.666569600
Null Arguments
Passing null for either argument results in null:
SET NULL 'null';
SELECT
NUMTODSINTERVAL(1, null),
NUMTODSINTERVAL(null, 'SECOND')
FROM DUAL;Result:
NUMTODSINTERVAL(1,NULL) NUMTODSINTERVAL(NULL,'SECOND') __________________________ _________________________________ null null
By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.
However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.
Missing Argument
Calling the function with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT NUMTODSINTERVAL()
FROM DUAL;Result:
Error starting at line : 1 in command - SELECT NUMTODSINTERVAL() FROM DUAL Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-00909: invalid number of arguments 00909. 00000 - "invalid number of arguments" *Cause: *Action: