Oracle NUMTOYMINTERVAL Function: A Simple Guide

When you need to perform date arithmetic in Oracle involving years or months, the NUMTOYMINTERVAL function is the right tool for the job.

This function is the companion to NUMTODSINTERVAL (which handles days, hours, minutes, and seconds). Oracle keeps these separate because "years" and "months" are logical units of time, not fixed durations (a month can be 28, 29, 30, or 31 days).

What is the NUMTOYMINTERVAL Function in Oracle?

The NUMTOYMINTERVAL(n, 'interval_unit') function converts a number n into a formal INTERVAL YEAR TO MONTH data type.

This INTERVAL is not a date; it's a duration or span of time (like "36 months" or "5 years"). You can then add this interval to, or subtract it from, any DATE or TIMESTAMP value.

NUMTOYMINTERVAL Function Syntax

The syntax for NUMTOYMINTERVAL is:

NUMTOYMINTERVAL(n, interval_unit)

Let's break that down:

• n: The number of units you want to convert (e.g., 1, 6, 18).
• interval_unit: A string specifying what unit the number n represents. This must be one of:
  • 'YEAR'
  • 'MONTH'

The unit is case-insensitive, so 'year' or 'month' also works.

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 NUMTOYMINTERVAL Function Examples

Here are two practical examples of how to use NUMTOYMINTERVAL.

Example 1: Adding Years to a Date with NUMTOYMINTERVAL

This example shows how to add a specific number of years to the current date to find a future anniversary.

Query:

SELECT
  SYSDATE AS "Today",
  SYSDATE + NUMTOYMINTERVAL(3, 'YEAR') AS "3_Years_From_Now"
FROM DUAL;

Result: (Assuming "Today" is 07-NOV-2025)

Today       3_Years_From_Now
----------- ----------------
07-NOV-2025 07-NOV-2028

Example 2: Filtering a Table with NUMTOYMINTERVAL

This is a very common use case. Let's find all employees who were hired within the last 18 months from the current date.

Query:

SELECT 
  last_name,
  hire_date
FROM employees
WHERE hire_date >= SYSDATE - NUMTOYMINTERVAL(18, 'MONTH');

Result: (The query will return a list of all employees hired in the 18-month period before the query was run.)