In Oracle Database, the TO_DSINTERVAL()
function converts its argument to a value of INTERVAL DAY TO SECOND
data type.
Syntax
The syntax goes like this:
TO_DSINTERVAL ( ' { sql_format | ds_iso_format } '
[ DEFAULT return_value ON CONVERSION ERROR ] )
So you have the option of passing the argument in SQL format or ISO format.
Here’s the difference:
sql_format
is SQL interval format compatible with the SQL standard (ISO/IEC 9075).ds_iso_format
is ISO duration format compatible with the ISO 8601:2004 standard.
The syntax for sql_format
goes like this:
[+ | -] days hours : minutes : seconds [. frac_secs ]
Where:
days
is an integer between 0 and 999999999hours
is an integer between 0 and 23minutes
andseconds
are integers between 0 and 59frac_secs
is the fractional part of seconds between .0 and .999999999.- One or more blanks separate days from hours. Additional blanks are allowed between format elements.
And the syntax for ds_iso_format
:
[-] P [days D]
[T [hours H] [minutes M] [seconds [. frac_secs] S ] ]
Where:
days
,hours
,minutes
andseconds
are integers between 0 and 999999999.frac_secs
is the fractional part of seconds between .0 and .999999999.- No blanks are allowed in the value.
- If you specify
T
, then you must specify at least one of thehours
,minutes
, orseconds
values.
Examples
Here are some examples to demonstrate.
SQL Format
Here, I pass the argument in SQL format:
SELECT TO_DSINTERVAL('135 08:35:47.123456789')
FROM DUAL;
Result:
+135 08:35:47.123456789
We can prepend it with its sign if necessary. Let’s flip it around to a negative:
SELECT TO_DSINTERVAL('-135 08:35:47.123456789')
FROM DUAL;
Result:
-135 08:35:47.123456789
ISO Format
In this example I pass the same value, but in ISO format:
SELECT TO_DSINTERVAL('P135DT08H35M47.123456789S')
FROM DUAL;
Result:
+135 08:35:47.123456789
Here it is with a negative value:
SELECT TO_DSINTERVAL('-P135DT08H35M47.123456789S')
FROM DUAL;
Result:
-135 08:35:47.123456789
Provide a Default Value on Conversion Error
You also have the option of providing a value to return in the event that there’s an error while converting the argument to an INTERVAL DAY TO SECOND
type.
Example:
SELECT
TO_DSINTERVAL(
'Oops!'
DEFAULT '00 00:00:00' ON CONVERSION ERROR
)
FROM DUAL;
Result:
+00 00:00:00.000000
Null Arguments
Passing null
results in null
:
SET NULL 'null';
SELECT
TO_DSINTERVAL(null)
FROM DUAL;
Result:
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.
Invalid Argument Count
Calling the function without passing any arguments, results in an error:
SELECT TO_DSINTERVAL()
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT TO_DSINTERVAL() FROM DUAL Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-00938: not enough arguments for function 00938. 00000 - "not enough arguments for function" *Cause: *Action:
And passing too many arguments results in an error:
SELECT TO_DSINTERVAL('P135D', 'P135D')
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT TO_DSINTERVAL('P135D', 'P135D') FROM DUAL Error report - ORA-12702: invalid NLS parameter string used in SQL function