How many days between two dates, how many weeks, how many months? In Oracle you have usually subtracted dates for days and used MONTHS_BETWEEN for months. DATEDIFF, new in Oracle AI Database 26ai, returns the difference in any unit with one function. It counts something specific, though: the number of unit boundaries crossed, not the number of complete units, and that changes the answer near the edges of a period.
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. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
datediff(time_unit, start_datetime, end_datetime [, start_of_period])
time_unit is YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, or NANOSECOND. The result is positive when end_datetime is later and negative when it is earlier. start_of_period is a number that sets where weeks begin, or the first month of a quarter or year.
Boundaries, Not Complete Units
The first query shows differences in several units; the second computes how late five flights departed.
Example:
select datediff(day, date '2026-01-01', date '2026-03-15') as days,
datediff(week, date '2026-01-01', date '2026-03-15') as weeks,
datediff(month, date '2026-01-31', date '2026-02-01') as month_boundaries,
datediff(year, date '2025-12-31', date '2026-01-01') as year_boundaries,
timestampdiff(minute, timestamp '2026-03-15 08:00:00',
timestamp '2026-03-15 10:45:00') as minutes
from dual;
select flight_no, datediff(minute, scheduled_departure, actual_departure) as minutes_late
from flights
where flight_id between 20 and 24;Output:
DAYS WEEKS MONTH_BOUNDARIES YEAR_BOUNDARIES MINUTES
_______ ________ ___________________ __________________ __________
73 11 1 1 165
FLIGHT_NO MINUTES_LATE
____________ _______________
NM149 2
NM133 0
NM121 0
NM142 69
NM118 17From 31 January to 1 February is one month, and from 31 December to 1 January is one year, although only a day passed in each case: one month boundary and one year boundary were crossed. Use DATEDIFF when you want calendar periods counted this way, such as "how many month-ends fall in between". For the number of complete months, use MONTHS_BETWEEN and truncate it.
Choose Where Weeks Start
The fourth argument decides where a week boundary falls. It is a number from 1 to 7 in ISO order, 1 for Monday through 7 for Sunday. From Saturday 14 March to Monday 16 March, a boundary is crossed only if weeks start on Sunday or Monday.
Example:
-- from Saturday 14 March to Monday 16 March 2026, with weeks starting on different days
select n as start_of_week,
datediff(week, date '2026-03-14', date '2026-03-16', n) as week_boundaries
from (values (1), (2), (3), (4), (5), (6), (7)) t (n);
select datediff(week, date '2026-03-14', date '2026-03-16') as default_weeks,
datediff(day, date '2026-03-16', date '2026-03-14') as negative_days
from dual;Output:
START_OF_WEEK WEEK_BOUNDARIES
________________ __________________
1 1
2 0
3 0
4 0
5 0
6 0
7 1
7 rows selected.
DEFAULT_WEEKS NEGATIVE_DAYS
________________ ________________
1 -2Only 1 (Monday) and 7 (Sunday) count a boundary. The second query shows that swapping the dates makes the result negative.
Things to Know
- For days between two DATE values without times, end_date - start_date gives the same number; with times, subtraction returns a fraction while DATEDIFF counts midnights crossed.
- TIMESTAMPDIFF is another name for DATEDIFF with the same arguments.
- A NULL argument returns NULL.
Related Guides
Conclusion
DATEDIFF returns the number of unit boundaries crossed between two dates or timestamps, in any unit from years to nanoseconds. Remember that one day can cross a month or year boundary, set the start of the week with the fourth argument, and use MONTHS_BETWEEN or date subtraction when you need complete units or fractions.
