Purpose
The NUMTODSINTERVAL function converts a numeric expression with a time interval unit into an INTERVAL DAY TO SECOND value. You can use this function to add or subtract a time interval from a datetime value.
Syntax
NUMTODSINTERVAL(number, 'interval_unit')
Parameters
Parameter |
Description |
|---|---|
| number | The number of the interval, which can be a NUMBER value or an expression that can be implicitly converted to a NUMBER value. |
| interval_unit | The unit of the interval, which can be a value of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The unit is case-insensitive. By default, the precision is 9. The following table describes the valid values of the interval unit:
|
Return type
INTERVAL DAY TO SECOND
Examples
Convert
100 dayto anINTERVAL DAY TO SECONDvalue.obclient> SELECT NUMTODSINTERVAL(100,'day') FROM DUAL; +-------------------------------+ | NUMTODSINTERVAL(100,'DAY') | +-------------------------------+ | +000000100 00:00:00.000000000 | +-------------------------------+ 1 row in setReturn 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