DATEDIFF returns the number of days, months or other units between two dates. Each database does it differently, and the examples cover PostgreSQL, MySQL, SQLite and SQL Server.
How to use DATEDIFF
DATEDIFF counts the days, months, years or other units from a start date to an end date. This is SQL Server’s version, the one most people mean:
DATEDIFF(unit, start_date, end_date)
SELECT DATEDIFF(day, '2026-01-01', '2026-03-15');
-- Output: 73
SELECT DATEDIFF(month, '2026-01-01', '2026-03-15');
-- Output: 2
SELECT DATEDIFF(day, '2026-03-15', '2026-01-01');
-- Output: -73
The unit can be year, quarter, month, week, day, hour, minute, second or smaller. When the end date comes before the start date, the result is negative.
With columns, you get one difference per row:
-- Input: orders
-- order_id | order_date | ship_date
-- ---------+------------+-----------
-- 1001 | 2026-02-02 | 2026-02-04
-- 1002 | 2026-02-03 | 2026-02-10
-- 1003 | 2026-02-27 | 2026-03-02
-- 1004 | 2026-03-05 | NULL
SELECT
order_id,
order_date,
ship_date,
DATEDIFF(day, order_date, ship_date) AS fulfillment_days
FROM orders;
-- Output:
-- order_id | order_date | ship_date | fulfillment_days
-- ---------+------------+------------+-----------------
-- 1001 | 2026-02-02 | 2026-02-04 | 2
-- 1002 | 2026-02-03 | 2026-02-10 | 7
-- 1003 | 2026-02-27 | 2026-03-02 | 3
-- 1004 | 2026-03-05 | NULL | NULL
Rules to know
-
It counts boundaries, not whole units.
DATEDIFF(month, ...)counts the month boundaries between the two dates, so a single day can count as a month, or a year:
SELECT DATEDIFF(month, '2026-01-31', '2026-02-01');
-- Output: 1
SELECT DATEDIFF(year, '2025-12-31', '2026-01-01');
-- Output: 1
-
NULL in, NULL out. If either date is NULL, so is the result, like order 1004 above. Wrap the call in
COALESCE()when you need a default.
Alternative syntax
MySQL has a two-argument DATEDIFF that only counts days and takes the end date first. For any other unit, use TIMESTAMPDIFF, which takes the start date first and counts whole units:
SELECT DATEDIFF('2026-03-15', '2026-01-01');
-- Output: 73
SELECT TIMESTAMPDIFF(MONTH, '2026-01-01', '2026-03-15');
-- Output: 2
PostgreSQL has no DATEDIFF. Subtract one date from another to get the days. For bigger units, AGE() (later date first) returns an interval in years, months and days, and EXTRACT pulls whole units out of it:
SELECT DATE '2026-03-15' - DATE '2026-01-01';
-- Output: 73
SELECT AGE('2026-03-15', '2026-01-01');
-- Output: 2 mons 14 days
SELECT EXTRACT(YEAR FROM AGE('2026-03-15', '2026-01-01')) * 12
+ EXTRACT(MONTH FROM AGE('2026-03-15', '2026-01-01'));
-- Output: 2
SQLite has no DATEDIFF either. julianday() turns a date into a number of days, so subtracting two of them gives the difference. CAST(... AS INTEGER) drops the fraction, and there’s no month or year unit, so divide by 30 or 365 for an approximation:
SELECT julianday('2026-03-15') - julianday('2026-01-01');
-- Output: 73.0
SELECT CAST((julianday('2026-03-15') - julianday('2026-01-01')) / 30 AS INTEGER);
-- Output: 2
-- The time of day counts: 8 p.m. to 8 a.m. is half a day.
-- Wrap each value in date() to count calendar days instead.
SELECT julianday('2026-03-16 08:00') - julianday('2026-03-15 20:00');
-- Output: 0.5
-- A date it can't read gives NULL, so store dates as YYYY-MM-DD
SELECT julianday('03/15/2026');
-- Output: NULL
When should you use DATEDIFF?
Most date difference questions fall into one of these four.
Measure how long something took
Subtract a start date from an end date to get a duration: the days it took to ship an order, the weeks a project ran, the minutes a support call lasted.
Count days overdue or remaining
Compare a deadline with today, or with a fixed reporting date, to see how late something is or how much time is left. Filter on the sign of the result to keep one side.
Express an age in months or years
Account age, customer tenure and length of service need a bigger unit than days. Use a month or year unit where your database has one. In SQLite, divide the days.
Find rows within a window around a date
Take the absolute value of the difference to find rows within a number of days of a date, before or after it: events near a launch, logins around an incident.
Real examples of DATEDIFF
Check support tickets against an SLA
Each ticket must be resolved within three days. Count the days each one took and flag the breaches (SQLite):
-- Input: support_tickets
-- ticket_id | created_at | resolved_at
-- ----------+------------+------------
-- 301 | 2026-03-02 | 2026-03-03
-- 302 | 2026-03-04 | 2026-03-07
-- 303 | 2026-03-05 | 2026-03-10
SELECT
ticket_id,
created_at,
resolved_at,
CAST(julianday(resolved_at) - julianday(created_at) AS INTEGER) AS resolution_days,
CASE
WHEN julianday(resolved_at) - julianday(created_at) <= 3 THEN 'Within SLA'
ELSE 'SLA Breach'
END AS sla_status
FROM support_tickets;
-- Output:
-- ticket_id | created_at | resolved_at | resolution_days | sla_status
-- ----------+------------+-------------+-----------------+-----------
-- 301 | 2026-03-02 | 2026-03-03 | 1 | Within SLA
-- 302 | 2026-03-04 | 2026-03-07 | 3 | Within SLA
-- 303 | 2026-03-05 | 2026-03-10 | 5 | SLA Breach
List overdue invoices
Show the invoices past their due date, most overdue first (MySQL):
-- Input: invoices
-- invoice_id | customer | due_date
-- -----------+---------------+-----------
-- 5001 | Acme Hardware | 2026-08-15
-- 5002 | Lumen Design | 2026-09-30
-- 5003 | Acme Hardware | 2026-10-01
-- 5004 | Cedar Clinic | 2026-10-31
SELECT
invoice_id,
customer,
due_date,
DATEDIFF(CURDATE(), due_date) AS days_overdue
FROM invoices
WHERE due_date < CURDATE()
ORDER BY days_overdue DESC;
-- Output (run on 2026-10-13):
-- invoice_id | customer | due_date | days_overdue
-- -----------+---------------+------------+-------------
-- 5001 | Acme Hardware | 2026-08-15 | 59
-- 5002 | Lumen Design | 2026-09-30 | 13
-- 5003 | Acme Hardware | 2026-10-01 | 12
Group customers by how long they’ve been with you
Put customers in tiers by their tenure in complete years (PostgreSQL):
-- Input: customers
-- customer_name | signup_date
-- ------------------+------------
-- Northwind Traders | 2022-11-05
-- Bluebird Bakery | 2025-06-20
-- Fjord Outfitters | 2025-12-20
-- Kestrel Labs | 2026-03-02
SELECT
customer_name,
signup_date,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, signup_date)) AS years_with_us,
CASE
WHEN AGE(CURRENT_DATE, signup_date) < INTERVAL '1 year' THEN 'New'
WHEN AGE(CURRENT_DATE, signup_date) < INTERVAL '2 years' THEN 'Established'
ELSE 'Loyal'
END AS customer_tier
FROM customers;
-- Output (run on 2026-10-13):
-- customer_name | signup_date | years_with_us | customer_tier
-- ------------------+-------------+---------------+--------------
-- Northwind Traders | 2022-11-05 | 3 | Loyal
-- Bluebird Bakery | 2025-06-20 | 1 | Established
-- Fjord Outfitters | 2025-12-20 | 0 | New
-- Kestrel Labs | 2026-03-02 | 0 | New
Find subscriptions up for renewal
List the subscriptions that end in the next 30 days, with the days left (SQL Server). The WHERE clause compares subscription_end with a date range instead of wrapping it in DATEDIFF, so an index on the column can still be used.
-- Input: subscriptions
-- customer_name | subscription_end
-- -----------------+-----------------
-- Harbor Dental | 2026-10-09
-- Maple Yoga | 2026-10-20
-- Orbit Analytics | 2026-11-07
-- Pinecrest Realty | 2027-01-15
SELECT
customer_name,
subscription_end,
DATEDIFF(day, GETDATE(), subscription_end) AS days_remaining
FROM subscriptions
WHERE subscription_end BETWEEN CAST(GETDATE() AS date)
AND DATEADD(day, 30, CAST(GETDATE() AS date))
ORDER BY days_remaining;
-- Output (run on 2026-10-13):
-- customer_name | subscription_end | days_remaining
-- ----------------+------------------+---------------
-- Maple Yoga | 2026-10-20 | 7
-- Orbit Analytics | 2026-11-07 | 25
Further reading about DATEDIFF
On this blog:
-
SQL Date Format Guide: turning dates into text with
strftime,TO_CHAR,DATE_FORMATandFORMAT - SQL Insert Date Guide: date types and how to insert date values
- How to Subtract in SQL: subtracting numbers, dates and timestamps
-
How to Subtract in PostgreSQL: date and timestamp arithmetic, including
age() -
SQLite Subtract Operations: date differences with
julianday() - Extract total days, hours or minutes from a PostgreSQL interval: turning intervals into numbers
- SQL COALESCE Guide: defaults for NULL dates
Official documentation:
- PostgreSQL: Date/Time Functions and Operators
- MySQL: DATEDIFF() and TIMESTAMPDIFF()
- SQLite: Date and Time Functions
- SQL Server: DATEDIFF
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."