Oracle NUMTOYMINTERVAL() Function

Oracle NUMTOYMINTERVAL() is a built-in function that converts a given number to INTERVAL YEAR TO MONTH text.

Oracle NUMTOYMINTERVAL() Syntax

Here is the syntax for the Oracle NUMTOYMINTERVAL() function:

NUMTOYMINTERVAL(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 a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. It indicates the unit of n, which must be one of the following values and is case-insensitive:

  • 'YEAR'
  • 'MONTH'

Return Value

The Oracle NUMTOYMINTERVAL() function returns an INTERVAL YEAR TO MONTH text that is converted from the given number.

By default, the precision of the returned value is 9.

If any argument is NULL, NUMTOYMINTERVAL() returns NULL.

Oracle NUMTOYMINTERVAL() Examples

Here are some examples that demonstrate the usage of the Oracle NUMTOYMINTERVAL() function.

Year

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

sqlCopy

SELECT
  NUMTOYMINTERVAL(1, 'YEAR')
FROM dual;

Output:

NUMTOYMINTERVAL(1,'YEAR')
____________________________
+01-00

Month

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

SELECT
  NUMTOYMINTERVAL(2, 'MONTH')
FROM dual;

Output:

NUMTOYMINTERVAL(2,'MONTH')
_____________________________
+00-02

NULL Argument

If any argument is NULL, NUMTOYMINTERVAL() returns NULL.

SET NULL 'NULL';
SELECT
    NUMTOYMINTERVAL(NULL, 'MONTH') NULL_1,
    NUMTOYMINTERVAL(1, NULL) NULL_2,
    NUMTOYMINTERVAL(NULL, NULL) NULL_3
FROM dual;

Output:

NULL_1    NULL_2    NULL_3
_________ _________ _________
NULL      NULL      NULL

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

Conclusion

The Oracle NUMTOYMINTERVAL() is a built-in function that converts a given number to INTERVAL YEAR TO MONTH text.