In Oracle Database, the NUMTOYMINTERVAL() function converts a number to an INTERVAL YEAR TO MONTH literal.

Syntax

The syntax goes like this:

NUMTOYMINTERVAL(n, 'interval_unit')

The n argument is the number for which to convert.

The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

• ‘YEAR‘
• ‘MONTH‘

The interval_unit argument is case insensitive.

Example

Here’s an example to demonstrate:

SELECT NUMTOYMINTERVAL(1, 'MONTH')
FROM DUAL;

Result:

+00-01

Here’s another one that uses the year:

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

Result:

+01-00

Fractions

Here’s one that contains a fractional part in the first argument:

SELECT 
    NUMTOYMINTERVAL(10.56, 'YEAR') AS "Year",
    NUMTOYMINTERVAL(10.56, 'MONTH') AS "Month"
FROM DUAL;

Result:

     Year     Month 
_________ _________ 
+10-07    +00-11    

Case Sensitivity

The second argument is case insensitive:

SELECT 
    NUMTOYMINTERVAL(150, 'YEAR') AS "YEAR",
    NUMTOYMINTERVAL(150, 'year') AS "year"
FROM DUAL;

Result:

      YEAR       year 
__________ __________ 
+150-00    +150-00    

Null Arguments

Passing null for either argument results in null:

SET NULL 'null';
SELECT 
    NUMTOYMINTERVAL(1, null),
    NUMTOYMINTERVAL(null, 'year')
FROM DUAL;

Result:

   NUMTOYMINTERVAL(1,NULL)    NUMTOYMINTERVAL(NULL,'YEAR') 
__________________________ _______________________________ 
null                       null                           

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Argument

Calling the function with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT NUMTOYMINTERVAL()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NUMTOYMINTERVAL()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: