TO_DSINTERVAL

Purpose

TO_DSINTERVAL() converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL DAY TO SECOND data type. This function can be used to perform addition and subtraction operations on a datetime 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

Return type

The return type is INTERVAL DAY TO SECOND.

Examples

• The following example returns the datetime 100 days after November 11, 2021 in the 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 set
  

• The following example returns the datetime 100 days and 10 hours after December 12, 2021 in the 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