TO_DSINTERVAL() Function in Oracle

by

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 999999999
  • hours is an integer between 0 and 23
  • minutes and seconds are integers between 0 and 59
  • frac_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 and seconds 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 the hours, minutes, or seconds 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