Purpose
This function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND value. You can use this function to add or subtract a date and time value.
Syntax
/*SQL date format*/
TO_DSINTERVAL ('[+ | -] days hours:minutes:seconds[.frac_secs]')
/*ISO date format*/
TO_DSINTERVAL ('[-] P[days D]
[T[hours H][minutes M][seconds[.frac_secs]S]]')
Parameters
| Parameter | Description |
|---|---|
| [+ | -]days hours:minutes:seconds[.frac_secs] | A string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type that conforms to this parameter format.
|
| [-] P[days D] [T[hours H][minutes M][seconds[.frac_secs]S] | A string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type that conforms to this parameter format. Notice The value cannot contain spaces. |
| frac_secs | The decimal part of the seconds. It is an optional parameter. It is an integer in the range of [0,999999999]. |
Return type
INTERVAL DAY TO SECOND
Examples
Use the SQL date format to return the date and time value 100 days after November 11, 2021.
obclient> SELECT TO_CHAR(TIMESTAMP '2021-11-11 00:00:00'+TO_DSINTERVAL('100 00:00:00'), 'YYYY-MM-DD HH24:MI:SS') "Time Stamp" FROM DUAL; +---------------------+ | Time Stamp | +---------------------+ | 2022-02-19 00:00:00 | +---------------------+ 1 row in setUse the ISO date format to return the date and time value 100 days and 10 hours after December 12, 2021.
obclient> SELECT TO_CHAR(TIMESTAMP '2021-11-11 00:00:00'+TO_DSINTERVAL('P100DT5H'), 'YYYY-MM-DD HH24:MI:SS') "Time Stamp" FROM DUAL; +---------------------+ | Time Stamp | +---------------------+ | 2022-02-19 05:00:00 | +---------------------+ 1 row in set