Oracle TO_YMINTERVAL Function: A Simple Guide

The TO_YMINTERVAL function in Oracle SQL is a conversion function that converts a text string (a CHAR or VARCHAR2) into an INTERVAL YEAR TO MONTH data type.

This function is the standard way to create a "span of time" based on years and months (like "1 year and 6 months") from a string, which you can then add to or subtract from DATE or TIMESTAMP values.

This is the companion to TO_DSINTERVAL, which handles days, hours, minutes, and seconds.

What is the TO_YMINTERVAL Function in Oracle?

The TO_YMINTERVAL(char) function "translates" a formatted string into an interval. This is essential for date arithmetic involving logical units like years and months.

The function can understand two different string formats:

1. SQL Interval Format: A human-readable format like 'YY-MM', for example: '01-02' (1 year, 2 months).
2. ISO 8601 Duration Format: A standard format like 'P[years]Y[months]M', for example: 'P1Y2M'.

A key feature of this function is the optional DEFAULT ... ON CONVERSION ERROR clause, which lets you provide a fallback value if the string can't be converted, preventing your query from failing.

TO_YMINTERVAL Function Syntax

The syntax for TO_YMINTERVAL is:

TO_YMINTERVAL(char [DEFAULT return_value ON CONVERSION ERROR])

Let's break that down:

• char: The text string you want to convert (e.g., '01-02' or 'P1Y2M').
• DEFAULT return_value ON CONVERSION ERROR (Optional): This is a modern, safe feature. It provides a fallback interval (as a string) to return if the char string doesn't match the format, preventing the query from failing.

A Note on Viewing Results

When you add or subtract an interval to a DATE value, the result is still a DATE. You may want to set your session's date format to see the results clearly.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

Oracle TO_YMINTERVAL Function Examples

Here are two practical examples of how to use TO_YMINTERVAL.

Example 1: Adding a Year-Month Interval to a Date using TO_YMINTERVAL

This example shows how to add a duration of "1 year and 2 months" to an employee's hire date to find their 14-month anniversary. We use the SQL format string '01-02'.

Query:

SELECT
  hire_date,
  hire_date + TO_YMINTERVAL('01-02') AS "Plus_14_Months"
FROM employees
WHERE ROWNUM <= 3; -- Just show 3 rows for this example

Result: (The results will depend on your employee data)

HIRE_DATE   Plus_14_Months
----------- ----------------
17-JUN-2003 17-AUG-2004
21-SEP-2005 21-NOV-2006
13-JAN-2001 13-MAR-2002

Example 2: Handling Conversion Errors with TO_YMINTERVAL

This example uses the DEFAULT ON CONVERSION ERROR clause. The string '1x-02' is invalid (it contains the letter 'x'), so it would normally cause an error.

Here, we tell Oracle to return a default interval of '00-00' (zero) if the conversion fails.

Query:

SELECT
  TO_YMINTERVAL(
    '1x-02' DEFAULT '00-00' ON CONVERSION ERROR
  ) AS "Interval_Value"
FROM DUAL;

Result: (Instead of an error, the query returns the default value, which is a zero-length interval.)

Interval_Value
--------------
+000000000-00