How to Use DATEDIFF() in SQL Server

How do I calculate the difference between two dates in SQL Server?

DATEDIFF() returns the number of specified datepart boundaries crossed between two dates.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does DATEDIFF() do?

DATEDIFF() counts how many datepart boundaries (day, month, year, etc.) occur between a start date and an end date. It returns an integer that can be positive, zero, or negative.

How do I write a simple DATEDIFF() query?

Place the desired datepart first, followed by the earlier date, then the later date. The order matters: DATEDIFF(day, startdate, enddate) returns positive numbers when enddate is after startdate.

Example: days between customer sign-up and first order

SELECT c.id, DATEDIFF(day, c.created_at, o.order_date) AS days_to_first_order FROM Customers c JOIN Orders o ON o.customer_id = c.id WHERE o.order_date = (SELECT MIN(order_date) FROM Orders WHERE customer_id = c.id);

Which datepart values can I use with DATEDIFF()?

Common dateparts include millisecond, second, minute, hour, day, week, month, quarter, and year. Use abbreviations like dd, wk, or yy interchangeably.

How do I filter rows by a date difference?

Wrap DATEDIFF() in a WHERE clause. Example: find orders shipped more than 7 days after placement: SELECT * FROM Orders WHERE DATEDIFF(day, order_date, shipped_date) > 7;

How do I calculate age in years?

Use year as the datepart and pass the birthdate first: SELECT DATEDIFF(year, birthdate, GETDATE()) AS age_years FROM Customers;. For precise ages, compare month/day portions separately.

Best practices for DATEDIFF()

Index date columns for faster filtering. Always choose the smallest necessary datepart (e.g., hours instead of minutes) to avoid large numbers. Confirm time-zone consistency to prevent off-by-one errors.

Why How to Use DATEDIFF() in SQL Server is important

How to Use DATEDIFF() in SQL Server Example Usage


-- Average days between order placement and shipping
SELECT AVG(DATEDIFF(day, o.order_date, o.shipped_date)) AS avg_days_to_ship
FROM Orders o;

How to Use DATEDIFF() in SQL Server Syntax


DATEDIFF ( datepart , startdate , enddate )
-- datepart: year|yy|yyyy, quarter|qq|q, month|mm|m, dayofyear|dy|y, day|dd|d, week|wk|ww, weekday|dw, hour|hh, minute|mi|n, second|ss|s, millisecond|ms, microsecond|mcs, nanosecond|ns
-- Example using ecommerce tables:
SELECT DATEDIFF(day, c.created_at, o.order_date) AS days_to_first_order
FROM Customers c
JOIN Orders o ON o.customer_id = c.id;

Common Mistakes

Using the wrong date order. Reversing startdate and enddate flips the sign, leading to negative or misleading results. Always pass the earlier date second when you expect positive values.
Comparing incompatible data types. Passing a VARCHAR column without casting to DATE causes implicit conversions and potential index scans. Explicitly CAST or CONVERT strings to proper date types first.

Frequently Asked Questions (FAQs)

Does DATEDIFF() include the start and end dates?

DATEDIFF() counts boundaries crossed, not elapsed time, so DATEDIFF(day, '2024-06-01', '2024-06-02') returns 1 even though only 24 hours pass.

Is DATEDIFF() affected by time zones?

DATEDIFF() uses stored datetime values without adjusting for client time zones. Convert all timestamps to UTC or the same zone before comparison.

How do I get fractional differences?

DATEDIFF() only returns integers. For fractional days, divide the difference in minutes or seconds by 1440 or 86400, or use DATEDIFF_BIG with microsecond for higher precision.