Oracle NUMTODSINTERVAL Function: A Simple Guide

When working with DATE or TIMESTAMP values in Oracle, you'll often need to add or subtract a specific duration, like "90 days" or "4 hours."

While you can add days by just date + 90, this becomes clunky for smaller units. The NUMTODSINTERVAL function is the modern, clear, and powerful way to handle this. It converts a simple number into a formal "time interval" or duration that Oracle understands.

What is the NUMTODSINTERVAL Function in Oracle?

The NUMTODSINTERVAL(n, 'interval_unit') function takes a number n and converts it into an INTERVAL DAY TO SECOND data type.

This INTERVAL is not a date; it's a span of time. For example, NUMTODSINTERVAL(30, 'MINUTE') creates a duration of "30 minutes."

This is extremely useful because it's the standard way to perform precise date arithmetic, especially in WHERE clauses or analytic functions.

NUMTODSINTERVAL Function Syntax

The syntax for NUMTODSINTERVAL is:

NUMTODSINTERVAL(n, interval_unit)

Let's break that down:

• n: The number of units you want to convert (e.g., 100, 4.5).
• interval_unit: A string specifying what unit the number n represents. This must be one of:
  • 'DAY'
  • 'HOUR'
  • 'MINUTE'
  • 'SECOND'

A Note on Viewing Results

When you add or subtract an interval to a DATE value, the result is still a DATE. To see the time component (hours, minutes, seconds) in your result, you may need to run this command in your SQL session first:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Oracle NUMTODSINTERVAL Function Examples

Here are two practical examples of how to use NUMTODSINTERVAL.

Example 1: Using NUMTODSINTERVAL for Date Arithmetic

This example shows how to add a specific duration to the current SYSDATE. Let's find the exact date and time 4 hours and 30 minutes from now.

Query:

SELECT
  SYSDATE AS "Now",
  
  SYSDATE + NUMTODSINTERVAL(4, 'HOUR') + NUMTODSINTERVAL(30, 'MINUTE') 
    AS "In_4_Hours_30_Mins"
FROM DUAL;

Result: (Assuming "Now" is 06-NOV-2025 09:00:00)

Now                  In_4_Hours_30_Mins
-------------------- --------------------
06-NOV-2025 09:00:00 06-NOV-2025 13:30:00

Example 2: Filtering a Table with NUMTODSINTERVAL

This is a very common use case. Let's find all employees who were hired within the last 90 days. We can't just write WHERE hire_date >= SYSDATE - 90 in some complex queries, but we can use an interval.

Query:

SELECT 
  last_name,
  hire_date
FROM employees
WHERE hire_date >= SYSDATE - NUMTODSINTERVAL(90, 'DAY');

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