Oracle TO_DSINTERVAL Function: A Simple Guide

The TO_DSINTERVAL function in Oracle SQL is a conversion function that converts a text string (a CHAR or VARCHAR2) into an INTERVAL DAY TO SECOND data type.

This function is the standard way to create a precise "span of time" (like "10 days and 2 hours") from a string, which you can then add to or subtract from DATE or TIMESTAMP values.

What is the TO_DSINTERVAL Function in Oracle?

The TO_DSINTERVAL(char) function "translates" a formatted string into an interval. This is essential for date arithmetic when you need more precision than just adding or subtracting whole numbers (which Oracle treats as "days").

The function can understand two different string formats:

1. SQL Interval Format: A human-readable format like 'DD HH:MI:SS.FF', for example: '10 08:30:00' (10 days, 8 hours, 30 minutes).
2. ISO 8601 Duration Format: A standard format like 'P[days]DT[hours]H[minutes]M[seconds]S', for example: 'P10DT8H30M'.

A key feature of this function is the optional DEFAULT ... ON CONVERSION ERROR clause, which lets you provide a fallback value if the string can't be converted, preventing your query from failing.

TO_DSINTERVAL Function Syntax

The syntax for TO_DSINTERVAL is:

TO_DSINTERVAL(char [DEFAULT return_value ON CONVERSION ERROR])

Let's break that down:

• char: The text string you want to convert (e.g., '100 00:00:00').
• DEFAULT return_value ON CONVERSION ERROR (Optional): This is a modern, safe feature. It provides a fallback interval (as a string) to return if the char string doesn't match the format, preventing the query from failing.

A Note on Viewing Results

To see the time component in your results when you perform date arithmetic, you may need to run this command in your SQL session first:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Oracle TO_DSINTERVAL Function Examples

Here are two practical examples of how to use TO_DSINTERVAL.

Example 1: Adding a 10-Day Interval to a Date using TO_DSINTERVAL

This is the most common use case. We want to find the exact time 10 days, 8 hours, and 30 minutes from now. We use the SQL format string '10 08:30:00' for this.

Query:

SELECT
  SYSDATE AS "Now",
  SYSDATE + TO_DSINTERVAL('10 08:30:00') AS "Future_Time"
FROM DUAL;

Result: (Assuming "Now" is 07-NOV-2025 09:00:00)

Now                  Future_Time
-------------------- --------------------
07-NOV-2025 09:00:00 17-NOV-2025 17:30:00

Example 2: Handling Conversion Errors with TO_DSINTERVAL

This example uses the DEFAULT ON CONVERSION ERROR clause. The string '1o 1:02:10' is invalid (it contains the letter 'o' instead of a zero), so it would normally cause an error.

Here, we tell Oracle to return a default interval of '10 08:00:00' if the conversion fails.

Query:

SELECT
  TO_DSINTERVAL(
    '1o 1:02:10' DEFAULT '10 08:00:00' ON CONVERSION ERROR
  ) AS "Interval_Value"
FROM DUAL;

Result: (Instead of an error, the query returns the default value, which is 10 days and 8 hours.)

Interval_Value
-----------------------------------
+000000010 08:00:00.000000000