SQL Reference ยท Date and Time Functions
SQL DATEDIFF() and date differences
How to calculate the difference between two dates in SQL: DATEDIFF in MySQL and SQL Server, date subtraction in PostgreSQL and Oracle, julianday in SQLite.
Finding how many days, months or years lie between two dates is one of the most common date calculations, and it is also one of the least portable. The standard defines no single function for it. MySQL and SQL Server both have a function called DATEDIFF(), but the two behave differently, PostgreSQL and Oracle subtract dates with the minus operator, and SQLite uses julianday().
This page shows each approach side by side. The most important trap: "difference in months" or "difference in years" can mean two things, counting calendar boundaries crossed or counting complete elapsed units, and different databases pick different meanings.
-- SQLite: whole days between two dates SELECT julianday('2025-03-01') - julianday('2025-01-01');
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- SQLite julianday(end_date) - julianday(start_date) -- days (can be fractional) unixepoch(end_ts) - unixepoch(start_ts) -- seconds -- PostgreSQL end_date - start_date -- integer days for DATE values age(end_ts, start_ts) -- interval of years, months, days -- MySQL DATEDIFF(end_date, start_date) -- days, order: later date first TIMESTAMPDIFF(unit, start_ts, end_ts) -- complete units -- SQL Server DATEDIFF(unit, start_date, end_date) -- counts unit boundaries crossed -- Oracle end_date - start_date -- days, may be fractional MONTHS_BETWEEN(end_date, start_date) -- fractional months
| Part | Description |
|---|---|
unit |
year, quarter, month, day, hour, minute, second and so on. Spelling and the available list differ by product. |
start, end |
Two dates or timestamps. Note that the argument order differs between MySQL and SQL Server. |
Returns: a number (or an interval in PostgreSQL's age()). A negative number means the "end" is earlier than the "start".
Dialect differences
| Task | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Days between | julianday(b) - julianday(a) |
b - a |
DATEDIFF(b, a) |
DATEDIFF(day, a, b) |
b - a |
| Complete months | Compute by hand | age(b, a) |
TIMESTAMPDIFF(MONTH, a, b) |
Compare with DATEADD (see below) |
MONTHS_BETWEEN(b, a) |
| Unit counting | n/a | age() is complete units |
TIMESTAMPDIFF is complete units |
DATEDIFF counts boundaries |
MONTHS_BETWEEN is fractional |
| Argument order | n/a | n/a | DATEDIFF(later, earlier) |
DATEDIFF(unit, earlier, later) |
n/a |
-- PostgreSQL SELECT DATE '2025-03-01' - DATE '2025-01-01'; -- 59 SELECT age(DATE '2025-06-15', DATE '2016-03-01'); -- 9 years 3 mons 14 days SELECT EXTRACT(YEAR FROM age(DATE '2025-06-15', DATE '2016-03-01')); -- 9 -- MySQL SELECT DATEDIFF('2025-03-01', '2025-01-01'); -- 59 SELECT TIMESTAMPDIFF(YEAR, '2016-03-01', '2025-06-15'); -- 9 -- SQL Server SELECT DATEDIFF(day, '2025-01-01', '2025-03-01'); -- 59 SELECT DATEDIFF(year, '2024-12-31', '2025-01-01'); -- 1, although only one day passed -- Oracle SELECT DATE '2025-03-01' - DATE '2025-01-01' FROM dual; -- 59 SELECT MONTHS_BETWEEN(DATE '2025-03-01', DATE '2025-01-01') FROM dual; -- 2
Usage notes
- Check the argument order every time. MySQL's two-argument
DATEDIFF(a, b)computesa - b. SQL Server'sDATEDIFF(unit, a, b)computesb - a. Moving a query between them silently flips the sign. - SQL Server counts boundaries.
DATEDIFF(year, '2024-12-31', '2025-01-01')is 1 because the year number changed. For "complete years", compare withDATEADDor adjust by one when the anniversary has not passed. - MySQL's
DATEDIFFignores the time part. It compares dates only. UseTIMESTAMPDIFForTIME_TO_SEC(TIMEDIFF(...))when hours matter. - SQLite returns fractional days when the values have times.
CAST(... AS INTEGER)truncates toward zero, andROUND()rounds. Both inputs must be valid ISO text; an invalid date makesjulianday()returnNULL. - Months and years are not fixed lengths. Dividing a day count by 30 or 365 is only an estimate. Use calendar arithmetic when you need exact months.
NULLin,NULLout. If either date is missing, the difference isNULL.- Filtering by distance from today is faster on the raw column. Prefer
WHERE hire_date <= date('now', '-5 years')overWHERE julianday('now') - julianday(hire_date) > 1826, because the first can use an index onhire_date.
Related
- Date and time storage,
CAST,NULLlogic - Lessons: Date functions, Types and CAST