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: