Purpose
You can call this function to convert a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 string into a value of the INTERVAL DAY TO SECOND data type. This function can be used in the addition or subtraction of 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 the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type that complies with the parameter format.
|
| [-] P[days D] [T[hours H][minutes M][seconds[.frac_secs]S] | A string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type that complies with the parameter format. Notice Spaces are not allowed in the value. |
| frac_secs | (Optional) Specifies the fractional part of seconds. The value is an integer value in the range of [0,999999999]. |
Return types
This function returns the INTERVAL DAY TO SECOND data type.
Examples
Return the datetime value of
100days after November 11, 2021 in SQL format.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 setReturn the datetime value of
100days and10hours after December 12, 2021 in ISO format.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