Purpose
NUMTODSINTERVAL() adds an interval unit to a numeric expression to convert the expression 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
NUMTODSINTERVAL(number, 'interval_unit')
Parameters
| Parameter | Description |
|---|---|
| number | The number of intervals. It is a NUMBER value or an expression that can be implicitly converted to a NUMBER value. |
| interval_unit | The interval unit. It can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. It is case-insensitive. By default, the precision of the return value is 9. The following interval units are supported:
|
Return type
The return type is INTERVAL DAY TO SECOND.
Examples
The following example converts
100 dayto a value of theINTERVAL DAY TO SECONDdata type.obclient> SELECT NUMTODSINTERVAL(100,'day') FROM DUAL; +-------------------------------+ | NUMTODSINTERVAL(100,'DAY') | +-------------------------------+ | +000000100 00:00:00.000000000 | +-------------------------------+ 1 row in setThe following example returns the datetime value 180 days after December 31, 2021.
obclient> SELECT TO_CHAR(TIMESTAMP '2021-12-31 00:00:00'+NUMTODSINTERVAL(180,'day'),'YYYY-MM-DD HH24:MI:SS') "Time Stamp" FROM DUAL; +---------------------+ | Time Stamp | +---------------------+ | 2022-06-29 00:00:00 | +---------------------+ 1 row in set
