Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- SQLite: whole days between two dates
SELECT julianday('2025-03-01') - julianday('2025-01-01');

Try it

SQL Days between two dates in SQLite ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Whole years and months, counted two ways ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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
SQL
-- 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) computes a - b. SQL Server's DATEDIFF(unit, a, b) computes b - 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 with DATEADD or adjust by one when the anniversary has not passed.
  • MySQL's DATEDIFF ignores the time part. It compares dates only. Use TIMESTAMPDIFF or TIME_TO_SEC(TIMEDIFF(...)) when hours matter.
  • SQLite returns fractional days when the values have times. CAST(... AS INTEGER) truncates toward zero, and ROUND() rounds. Both inputs must be valid ISO text; an invalid date makes julianday() return NULL.
  • 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.
  • NULL in, NULL out. If either date is missing, the difference is NULL.
  • Filtering by distance from today is faster on the raw column. Prefer WHERE hire_date <= date('now', '-5 years') over WHERE julianday('now') - julianday(hire_date) > 1826, because the first can use an index on hire_date.