w3resource

Oracle TO_DSINTERVAL function

Description

The TO_DSINTERVAL() function is used to convert a character string of (CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to an INTERVAL DAY TO SECOND type.

The function accepts argument in one of the two formats:

  • SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
  • ISO duration format compatible with the ISO 8601:2004 standard
Uses of Oracle TO_DSINTERVAL() Function
  • Interval Conversion: Convert character strings to time intervals for day-to-second precision.

  • Date-Time Calculations: Perform arithmetic operations on date and time values using intervals.

  • Data Normalization: Standardize interval data across different formats for consistent processing.

  • Duration Representation: Represent precise time durations in reports or analyses.

  • Query Filtering: Use intervals to filter and retrieve data based on specific time-based conditions.

Syntax: SQL interval format

[+ | -] days hours : minutes : seconds [. frac_secs ]

In the SQL format:

  • 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.

Syntax: ISO duration format

[-] P [days D] 
T [hours H] [minutes M] [seconds [. frac_secs] S]

In the ISO format:

  • 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.

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example: Oracle TO_DSINTERVAL function

The following SQL query selects the employees who had worked for the company for at least 200 days on January 1, 2005 :

SQL> SELECT employee_id, last_name FROM employees
  2     WHERE hire_date + TO_DSINTERVAL('200 00:00:00')
  3     <= DATE '2005-01-01'
  4     ORDER BY employee_id;

Sample Output:

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        100 King
        102 De Haan
        108 Greenberg
        109 Faviet
        114 Raphaely
        115 Khoo
        122 Kaufling
        133 Mallin
        137 Ladwig
        141 Rajs
        156 King
...		

Previous: TO_TIMESTAMP_TZ
Next: TO_YMINTERVAL



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/oracle/datetime-functions/oracle-to_dsinterval-function.php