Oracle NUMTODSINTERVAL() Function

Oracle NUMTODSINTERVAL() is a built-in function that converts a given number to text in INTERVAL DAY TO SECOND format.

Oracle NUMTODSINTERVAL() Syntax

Here is the syntax of the Oracle NUMTODSINTERVAL() function:

NUMTODSINTERVAL(n, 'interval_unit')

Parameters

n

Required. It can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.

'interval_unit'

Required. It can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. It indicates the unit of n, and it must be one of the following values, and is case-insensitive:

  • 'DAY'
  • 'HOUR'
  • 'MINUTE'
  • 'SECOND'

Return Value

The Oracle NUMTODSINTERVAL() function returns text in INTERVAL DAY TO SECOND format converted from the given number.

By default, the returned precision is 9.

If any of the parameters is NULL, NUMTODSINTERVAL() returns NULL.

Oracle NUMTODSINTERVAL() Examples

Here are some examples that demonstrate how to use the Oracle NUMTODSINTERVAL() function.

Day

To create an interval value of 1 day, use the following statement:

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

Output:

NUMTODSINTERVAL(1,'DAY')
___________________________
+01 00:00:00.000000

Hour

To create an interval value of 2 hours, use the following statement:

SELECT
  NUMTODSINTERVAL(2, 'HOUR')
FROM dual;

Output:

NUMTODSINTERVAL(2,'HOUR')
____________________________
+00 02:00:00.000000

Minute

To create an interval value of 3 minutes, use the following statement:

SELECT
  NUMTODSINTERVAL(3, 'MINUTE')
FROM dual;

Output:

NUMTODSINTERVAL(3,'MINUTE')
______________________________
+00 00:03:00.000000

Second

To create an interval value of 4 seconds, use the following statement:

SELECT
  NUMTODSINTERVAL(4, 'SECOND')
FROM dual;

Output:

NUMTODSINTERVAL(4,'SECOND')
______________________________
+00 00:00:04.000000

You can provide a value with a fraction to include fractional seconds,

SELECT
  NUMTODSINTERVAL(4.123123, 'SECOND')
FROM dual;

Output:

NUMTODSINTERVAL(4,'SECOND')
______________________________
+00 00:00:04.000000

The maximum precision for fractional seconds is 9 digits, and if it exceeds 9 digits, the fractional seconds are rounded to 9 digits:

SELECT
  NUMTODSINTERVAL(4.1111111115, 'SECOND')
FROM dual;

Output:

NUMTODSINTERVAL(4.1111111115,'SECOND')
_________________________________________
+00 00:00:04.111111112

NULL Parameters

If either argument is NULL, NEXT_DAY() returns NULL.

SET NULL 'NULL';
SELECT
    NUMTODSINTERVAL(NULL, 'DAY') NULL_1,
    NUMTODSINTERVAL(1, NULL) NULL_2,
    NUMTODSINTERVAL(NULL, NULL) NULL_3
FROM dual;

Output:

NULL_1    NULL_2    NULL_3
_________ _________ _________
NULL      NULL      NULL

In this example, the SET NULL 'NULL'; statement is used to display NULL values as the string 'NULL'.

Conclusion

Oracle NUMTODSINTERVAL() is a built-in function that converts a given number to text in INTERVAL DAY TO SECOND format.