Oracle TO_DSINTERVAL() Function

Oracle’s TO_DSINTERVAL() is a built-in function that converts a given string parameter into a INTERVAL DAY TO SECOND type value.

Oracle TO_DSINTERVAL() Syntax

Here is the syntax of the Oracle TO_DSINTERVAL() function:

TO_DSINTERVAL(str, [ DEFAULT return_value ON CONVERSION ERROR ])

Parameters

str

Required. It can be any expression that specifies a string of data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2 whose computation results in an interval. You can use one of two formats:

• The SQL interval format compatible with the SQL standard (ISO/IEC 9075): [+|-] days hours:minutes:seconds[.frac_secs]. Here, days is an integer between 0 and 999999999, hours is an integer between 0 and 23, and minutes and seconds are integers between 0 and 59. The fraction of a second frac_secs is the decimal portion of the second and is between .0 and .999999999. One or more spaces separate days and hours. Additional spaces are allowed between format elements.

• The ISO duration format compatible with the ISO 8601:2004 standard: [-]P[daysD][T[hoursH][minutesM][seconds[.frac_secs]S]]. Here, days, hours, minutes, and seconds are integers between 0 and 999999999. The fraction of a second frac_secs is the decimal portion of the second and is between .0 and .999999999. No spaces are allowed in the value. If T is specified, at least one of hours, minutes, or seconds must be specified.

DEFAULT return_value ON CONVERSION ERROR

Optional. It allows you to specify the value to return if an error occurs during the conversion.

Return Value

The Oracle TO_DSINTERVAL() function returns a value of type INTERVAL DAY TO SECOND.

If either parameter is NULL, TO_DSINTERVAL() returns NULL.

Oracle TO_DSINTERVAL() Examples

Here are some examples that show how to use the Oracle TO_DSINTERVAL() function.

SQL Format

This example passes an interval value using the SQL format:

SELECT
    TO_DSINTERVAL('100 10:11:12.123')  "Result1",
    TO_DSINTERVAL('+100 10:11:12.123') "Result2",
    TO_DSINTERVAL('-100 10:11:12.123') "Result3"
FROM dual;

Output:

Result1                 Result2                 Result3
_______________________ _______________________ _______________________
+100 10:11:12.123000    +100 10:11:12.123000    -100 10:11:12.123000

ISO Format

This example passes an interval value using the ISO format:

SELECT
    TO_DSINTERVAL('P100DT10H11M12.123S')  "Result1",
    TO_DSINTERVAL('-P100DT10H11M12.123S') "Result2"
FROM dual;

Output:

Result1                 Result2
_______________________ _______________________
+100 10:11:12.123000    -100 10:11:12.123000

Error

If you provide an incorrect format, the Oracle TO_DSINTERVAL() function will report an error:

SELECT
    TO_DSINTERVAL('+P100DT10H11M12.123S')
FROM dual;

Output:

SQL Error: ORA-01867: the interval is invalid
01867. 00000 -  "the interval is invalid"
*Cause:    The character string you specified is not a valid interval.
*Action:   Please specify a valid interval.

In this example, Oracle reports an error because the ISO format does not support the + symbol. The Oracle TO_DSINTERVAL() function allows you to specify a default value to handle conversion errors:

SELECT
    TO_DSINTERVAL(
      '+P100DT10H11M12.123S'
      DEFAULT 'P100DT10H11M12.123S' ON CONVERSION ERROR
    ) "Result"
FROM dual;

Output:

Result
_______________________
+100 10:11:12.123000

NULL parameter

If any parameter is NULL, TO_DSINTERVAL() will return NULL.

SET NULL 'NULL';
SELECT
    TO_DSINTERVAL(NULL)
FROM dual;

Output:

TO_DSINTERVAL(NULL)
______________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Conclusion

The Oracle TO_DSINTERVAL() is a built-in function that converts the given string parameter to a value of type INTERVAL DAY TO SECOND.