TO_YMINTERVAL() Function in Oracle

by

In Oracle Database, the TO_YMINTERVAL() function converts its argument to a value of INTERVAL MONTH TO YEAR data type.

Syntax

The syntax goes like this:

TO_YMINTERVAL
  ( '  { [+|-] years - months 
       | ym_iso_format 
       } '
    [ DEFAULT return_value ON CONVERSION ERROR ]
  )

You have the option of passing the argument in SQL format or ISO format.

Here’s the difference:

  • sql_format is SQL interval format compatible with the SQL standard (ISO/IEC 9075).
  • ds_iso_format is ISO duration format compatible with the ISO 8601:2004 standard.

In SQL format, years is an integer between 0 and 999999999, and months is an integer between 0 and 11. Additional blanks are allowed between format elements.

In ISO format (syntax below), years and months are integers between 0 and 999999999. Days, hours, minutes, seconds, and frac_secs are non-negative integers, and are ignored, if specified. No blanks are allowed in the value. If you specify T, then you must specify at least one of the hours, minutes, or seconds values.

The syntax for ds_iso_format goes like this:

[-] P [ years Y ] [months M] [days D]
  [T [hours H] [minutes M] [seconds [. frac_secs] S ] ]

Examples

Here are some examples to demonstrate.

SQL Format

Here, I pass the argument in SQL format:

SELECT TO_YMINTERVAL('1-2')
FROM DUAL;

Result:

+01-02

In this case, I passed one year and two months in SQL format.

We can prepend it with its sign if necessary. Let’s flip it around to a negative:

SELECT TO_YMINTERVAL('-1-2')
FROM DUAL;

Result:

-01-02

ISO Format

In this example I pass the same value, but in ISO format:

SELECT TO_YMINTERVAL('P1Y2M')
FROM DUAL;

Result:

+01-02

Here it is with a negative value:

SELECT TO_YMINTERVAL('-P1Y2M')
FROM DUAL;

Result:

-01-02

Provide a Default Value on Conversion Error

You also have the option of providing a value to return in the event that there’s an error while converting the argument to an INTERVAL MONTH TO YEAR type.

Example:

SELECT 
    TO_YMINTERVAL(
    '1y-02m'
    DEFAULT '00-00' ON CONVERSION ERROR
    )
FROM DUAL;

Result:

+00-00

Null Arguments

Passing null results in null:

SET NULL 'null';
SELECT 
    TO_YMINTERVAL(null)
FROM DUAL;

Result:

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.

Invalid Argument Count

Calling the function without passing any arguments, results in an error:

SELECT TO_YMINTERVAL()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function

And passing too many arguments results in an error:

SELECT TO_YMINTERVAL('P1Y2M', 'P3Y5M')
FROM DUAL;

Result:

SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"