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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics