Oracle TO_YMINTERVAL() Function

Oracle TO_YMINTERVAL() is a built-in function that converts a given string parameter to a value of type INTERVAL MONTH TO YEAR.

Syntax of Oracle TO_YMINTERVAL()

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

TO_YMINTERVAL(str, [ DEFAULT return_value ON CONVERSION ERROR ])

Parameters

str

Required. It can be any expression that specifies a string with a data type of CHAR, VARCHAR2, NCHAR, or NVARCHAR2, whose computation results in a value that can be converted to the INTERVAL MONTH TO YEAR data type. You can use one of two formats:

• A SQL interval format that is compatible with the SQL standard (ISO/IEC 9075): [+|-] years - months. The years is an integer between 0 and 999999999, and months is an integer between 0 and 11. One or more extra spaces are allowed between the elements.

• An ISO duration format that is compatible with the ISO 8601:2004 standard: [-]P[yearsY] [monthsM][[daysD][T[hoursH][minutesM][seconds[.frac_secs]S]]. The minutes and seconds are integers between 0 and 999999999. The days, hours, minutes, seconds, and frac_secs are non-negative integers and are ignored. No spaces are allowed within the value. If you specify T, you must specify at least one of hours, minutes, or seconds.

DEFAULT return_value ON CONVERSION ERROR

Optional. It allows you to specify the value to be returned if an error occurs during the conversion.

Return Value

The Oracle TO_YMINTERVAL() function returns a value of type INTERVAL MONTH TO YEAR.

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

Examples of Oracle TO_YMINTERVAL()

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

SQL Format

This example passes an interval value in SQL format:

SELECT
    TO_YMINTERVAL('100 - 10')  "Result1",
    TO_YMINTERVAL('+100 - 10') "Result2",
    TO_YMINTERVAL('-100 - 10') "Result3"
FROM dual;

Output:

Result1    Result2    Result3
__________ __________ __________
+100-10    +100-10    -100-10

ISO Format

This example passes an interval value in ISO format:

SELECT
    TO_YMINTERVAL('P100Y10M')  "Result1",
    TO_YMINTERVAL('-P100Y10M')  "Result2",
    TO_YMINTERVAL('P100Y10M100DT10H11M12.123S')  "Result3"
FROM dual;

Output:

Result1    Result2    Result3
__________ __________ __________
+100-10    -100-10    +100-10

Errors

If you provide an incorrect format, the Oracle TO_YMINTERVAL() function reports an error:

SELECT
    TO_YMINTERVAL('+P100Y10M')
FROM dual;

Output:

SQL Error: ORA-01867: the interval is invalid
01867. 00000 -  "the interval is invalid"
*Cause:    The character string you specified is not a valid interval.
*Action:   Please specify a valid interval.

In this example, Oracle reported an error because the IOS format does not support the + symbol. The TO_YMINTERVAL() function in Oracle allows you to specify a default value to handle conversion errors:

SELECT
    TO_YMINTERVAL(
      '+P100Y10M'
      DEFAULT 'P100Y10M' ON CONVERSION ERROR
    ) "Result"
FROM dual;

Output:

Result
__________
+100-10

NULL parameter

If any parameter is NULL, TO_YMINTERVAL() will return NULL.

SET NULL 'NULL';
SELECT
    TO_YMINTERVAL(NULL)
FROM dual;

Output:

TO_YMINTERVAL(NULL)
______________________
NULL

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

Conclusion

Oracle’s TO_YMINTERVAL() is a built-in function that converts a given string parameter to a value of type INTERVAL MONTH TO YEAR.