T-SQL reference › DATEDIFF

DATEDIFF in SQL Server: the number of days between two dates

DATEDIFF counts how many boundaries of a unit you cross going from one date to another. That is not quite the same as how much time has passed.

Syntax

DATEDIFF(datepart, start_date, end_date)

The result is end - start: positive when the end is later.

Days since each customer’s last order

SELECT customer_id,
       MAX(order_date) AS last_order,
       DATEDIFF(day, MAX(order_date), GETDATE()) AS days_since
FROM Orders
GROUP BY customer_id
ORDER BY days_since DESC;

Boundaries, not elapsed time

DATEDIFF(year, '2025-12-31', '2026-01-01') is 1, even though one day passed. DATEDIFF(day, '2026-10-30 23:59', '2026-10-31 00:01') is 1, after two minutes. Use the unit you actually mean, and don’t use years to work out someone’s age.

Last 7 days with DATEDIFF

WHERE DATEDIFF(day, order_date, GETDATE()) BETWEEN 1 AND 7

This gives the same rows as a DATEADD range, but it wraps the column in a function, so SQL Server can’t use an index on order_date. On big tables, prefer the range form shown on the GETDATE page.

Try it on Harbour & Co

James Rowe (Sales Manager) needs this now: “Need last week’s orders over £50 for the 10am. Don’t include today. Newest first.” Write the query, run it, send the results. Free.

Take the ticket →