DATEDIFF()

Syntax

How it works

DATEDIFF() answers a common question: how far apart are two dates? The catch is that it is one of the least portable functions in SQL, because every engine implemented it differently. Learning the differences up front saves you from a whole class of subtle bugs.

In SQL Server, the form is DATEDIFF(datepart, startdate, enddate). You choose the unit yourself (day, month, year and so on), and it returns the number of times that boundary is crossed between the two dates. In MySQL, DATEDIFF(enddate, startdate) takes only two arguments, always returns whole days, and crucially puts the end date FIRST. For any other unit in MySQL you use TIMESTAMPDIFF(unit, start, end).

PostgreSQL has no DATEDIFF function at all. To get whole days you simply subtract one date from the other (enddate - startdate), which yields an integer. For richer results you use date arithmetic helpers such as AGE(end, start), which returns a years/months/days interval, or EXTRACT to pull a component out of that interval.

Examples

Days between two dates in SQL Server

-- datepart first, then start, then end
SELECT DATEDIFF(day, '2026-01-01', '2026-07-04') AS days_between;

days_between
------------
184

Days between two dates in MySQL (reversed order)

-- end date FIRST, then start date; always returns days
SELECT DATEDIFF('2026-07-04', '2026-01-01') AS days_between;

days_between
------------
184

Days between two dates in PostgreSQL

-- subtracting two dates yields an integer number of days
SELECT DATE '2026-07-04' - DATE '2026-01-01' AS days_between;

days_between
------------
184

Age in whole years

-- SQL Server: subtract, then correct if the birthday
-- has not happened yet this year
SELECT DATEDIFF(year, birth_date, GETDATE())
     - CASE WHEN (MONTH(birth_date) * 100 + DAY(birth_date))
              > (MONTH(GETDATE()) * 100 + DAY(GETDATE()))
             THEN 1 ELSE 0 END AS age_years
FROM customers;

age_years
---------
       34
       28
       41

Age in years in MySQL and PostgreSQL

-- MySQL: TIMESTAMPDIFF gives complete years directly
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age_years
FROM customers;

-- PostgreSQL: AGE returns an interval, EXTRACT the year
SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age_years
FROM customers;

age_years
---------
       34
       28
       41

Months between two dates

-- SQL Server
SELECT DATEDIFF(month, '2026-01-15', '2026-07-04') AS months;

-- MySQL
SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-07-04') AS months;

months (SQL Server) | months (MySQL)
--------------------+---------------
                  6 |             5

Common mistakes

-- Wrong: in MySQL this is end minus start, so
-- putting the earlier date first gives a NEGATIVE result
-- (MySQL) or a totally different meaning (SQL Server)
SELECT DATEDIFF('2026-01-01', '2026-07-04');  -- MySQL: -184

-- MySQL: end date FIRST
SELECT DATEDIFF('2026-07-04', '2026-01-01');  -- 184

-- SQL Server: start date FIRST (and a datepart)
SELECT DATEDIFF(day, '2026-01-01', '2026-07-04');  -- 184

-- Wrong assumption: this is NOT one full month apart.
-- SQL Server counts month BOUNDARIES crossed, not
-- 30-day periods, so Jan 31 to Feb 01 returns 1
SELECT DATEDIFF(month, '2026-01-31', '2026-02-01');  -- 1

-- If you need complete elapsed months, use TIMESTAMPDIFF
-- in MySQL, or subtract and check the day-of-month
-- yourself in SQL Server.
SELECT TIMESTAMPDIFF(MONTH, '2026-01-31', '2026-02-01');  -- 0

-- Wrong: wrapping the indexed column in DATEDIFF forces
-- a full scan; the index on order_date cannot be used
SELECT * FROM orders
WHERE DATEDIFF(day, order_date, GETDATE()) <= 30;

-- Right: keep the column bare and compare to a computed
-- bound so the index on order_date stays usable
SELECT * FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());

Performance

The biggest performance trap with DATEDIFF() is wrapping it around a column in a WHERE clause, as in WHERE DATEDIFF(day, order_date, GETDATE()) <= 30. This makes the predicate non-sargable: the engine must compute the function for every row and cannot seek an index on that column. Always move the arithmetic to the constant side with DATEADD so the column stays bare and the index can be used.

DATEDIFF itself is a cheap scalar operation on two values, so in a SELECT list it costs almost nothing. The expense is never the function; it is losing an index seek because a column was buried inside it. When you must group or filter by a derived period, consider a persisted computed column or an indexed date bucket instead of recomputing DATEDIFF on the fly.

Because behaviour and argument order differ across engines, put date math in one place (a view or a helper) so a migration does not scatter silent bugs. Test the boundary cases, especially month and year counts, since SQL Server counts boundaries crossed rather than whole elapsed units.

Interview questions

-- SQL Server
SELECT DATEDIFF(day, '2026-01-01', '2026-07-04');
-- MySQL
SELECT DATEDIFF('2026-07-04', '2026-01-01');
-- PostgreSQL
SELECT DATE '2026-07-04' - DATE '2026-01-01';

SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM customers;