Introducing DATEADD in Oracle AI Database 26ai

The DATEADD function in Oracle AI Database 26ai adds or subtracts a specified time interval from a date or timestamp. The following examples illustrate how to add or subtract different time intervals using DATEADD.

Syntax

DATEADD(time_unit, value, datetime)

The function accepts three arguments:

time_unit: The unit of time, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, or YEAR.
value: The number of units to add or subtract. Positive values add time, while negative values subtract time.
datetime: The starting date or timestamp.

Examples

1. Second

The following example subtracts 30 seconds from a timestamp.

SELECT DATEADD(SECOND, -30, TIMESTAMP '2026-01-15 10:30:00') AS SECOND;

SECOND
---------------------------------------
15-JAN-26 10.29.30.000000000 AM

2. Minute

This example adds 15 minutes to a timestamp.

SELECT DATEADD(MINUTE, 15, TIMESTAMP '2026-01-15 10:30:00') AS MINUTE;

MINUTE
-----------------------------------------
15-JAN-26 10.45.00.000000000 AM

3. Hour

The following example subtracts five hours from a timestamp.

SELECT DATEADD(HOUR, -5, TIMESTAMP '2026-01-15 10:30:00') AS HOUR;

HOUR
--------------------------------------
15-JAN-26 05.30.00.000000000 AM

4. Day

This example adds 10 days to a date.

SELECT DATEADD(DAY, 10, DATE '2026-01-15') AS DAY;

DAY
---------
25-JAN-26

5. Week

The following example adds two weeks to a date.

SELECT DATEADD(WEEK, 2, DATE '2026-01-15') AS WEEK;

WEEK
---------
29-JAN-26

6. Month

This example adds three months to a date.

SELECT DATEADD(MONTH, 3, DATE '2026-01-15') AS MONTH;

MONTH
---------
15-APR-26

7. Year

The following example adds two years to a date.

SELECT DATEADD(YEAR, 2, DATE '2026-01-15') AS YEAR;

YEAR
---------
15-JAN-28