NUMTODSINTERVAL

NUMTODSINTERVAL(n, ‘unit’)

• n: This is a number referencing the value of the interval to be converted. The supported types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, BINARY_FLOAT, and BINARY_DOUBLE.
• ‘unit’: This is a string specifying the unit that n is representing. Acceptable values include ‘SECOND’, ‘MINUTE’, ‘HOUR’, ‘DAY’, ‘MONTH’, ‘YEAR’. The units are case insensitive and can be specified in singular or plural form.

Example

SELECT NUMTODSINTERVAL(1, 'DAY') FROM dual;

Output

+-----------------+
| NUMTODSINTERVAL |
+-----------------+
| +1 00:00:00.000 |
+-----------------+

Explanation

In this example, NUMTODSINTERVAL function is used to convert number 1 into an interval day to second type where ‘DAY’ represents single day. The output “+1 00:00:00.000” represents 1 day.