Oracle has always added time with arithmetic: a number of days, an interval, or ADD_MONTHS. DATEADD, new in Oracle AI Database 26ai, adds a number of any unit, from years to nanoseconds, in one function with the same name and argument order as in other databases. That makes date arithmetic easier to read and SQL easier to port.
Code for This Guide
The main example is in the examples/date-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
dateadd(time_unit, value, datetime)
time_unit is YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, or NANOSECOND, written without quotes. value is the number of units, negative to go back. The result has the same data type as datetime.
Add Days, Months, Quarters, and Minutes
Example:
select dateadd(day, 3, date '2026-03-15') as plus_3_days,
dateadd(month, 1, date '2026-01-31') as plus_1_month,
dateadd(quarter, -1, date '2026-03-15') as minus_quarter,
dateadd(minute, 90, timestamp '2026-03-15 23:00:00') as plus_90_minutes
from dual;Output:
PLUS_3_DAYS PLUS_1_MONTH MINUS_QUARTER PLUS_90_MINUTES ______________ _______________ ________________ _______________________ 18-MAR-2026 28-FEB-2026 15-DEC-2025 16-MAR-2026 00:30:00
A month after 31 January is 28 February, because February has no 31st. Ninety minutes after 23:00 crosses midnight into the next day.
DATEADD Compared with ADD_MONTHS
The two differ at the end of a month. ADD_MONTHS treats the last day of a month as "the last day", so 28 February plus one month is 31 March. DATEADD keeps the day number when it can, so 28 February plus one month is 28 March.
Example:
select add_months(date '2026-02-28', 1) as add_months_feb28,
dateadd(month, 1, date '2026-02-28') as dateadd_feb28,
add_months(date '2026-01-31', 1) as add_months_jan31,
dateadd(month, 1, date '2026-01-31') as dateadd_jan31
from dual;
select dateadd(week, 2, date '2026-03-15') as plus_2_weeks,
dateadd(year, 1, date '2024-02-29') as plus_1_year,
dateadd(millisecond, 1500, timestamp '2026-03-15 08:30:00') as plus_1500_ms
from dual;Output:
ADD_MONTHS_FEB28 DATEADD_FEB28 ADD_MONTHS_JAN31 DATEADD_JAN31 ___________________ ________________ ___________________ ________________ 31-MAR-2026 28-MAR-2026 28-FEB-2026 28-FEB-2026 PLUS_2_WEEKS PLUS_1_YEAR PLUS_1500_MS _______________ ______________ _______________________ 29-MAR-2026 28-FEB-2025 15-MAR-2026 08:30:01
A year after 29 February 2024 is 28 February 2025. Adding 1,500 milliseconds to a TIMESTAMP adds 1.5 seconds; the display shows whole seconds because of the session's timestamp format.
Things to Know
- For a DATE, units smaller than a second are added but the result is truncated to whole seconds, since a DATE has no fractions.
- For a TIMESTAMP WITH TIME ZONE, adding hours or smaller units adds elapsed time, so the clock time can jump at a daylight saving change.
- The classic forms still work and are often shorter: d + 3 for days, d + interval '90' minute, add_months(d, 1).
Related Guides
Conclusion
DATEADD adds a number of years, quarters, months, weeks, days, hours, or smaller units to a date or timestamp and returns the same type. It keeps the day of the month when adding months, unlike ADD_MONTHS at month ends, and gives Oracle the same date function that other databases have.
