Time is crucial in data work. Knowing how long something took, figuring out the gap between two events, or checking how recent a change was — this comes up often. Whether you're logging user activity or tracking overdue tasks, measuring the difference between two dates is a common task in SQL.

That’s where DATEDIFF steps in. It’s not flashy, but it saves time and makes your queries clearer. You don’t need fancy tricks or math hacks — just one simple function. Let’s get into what DATEDIFF does, how to use it, and what to watch out for.

What is the SQL DATEDIFF Function?

DATEDIFF is a date function in SQL used to calculate the difference between two date values. It doesn’t return the number of seconds or exact time gap — instead, it gives you the count of the specified date part (like days, months, or years) between two dates. The key thing to understand is that the unit you specify controls how the result is calculated.

In most SQL dialects, the syntax looks like this:

DATEDIFF(datepart, start_date, end_date)

• datepart is the unit of time you want the result in (day, month, year, etc.).
• start_date is the beginning of the period.
• end_date is the end of the period.

The function returns an integer. A positive result means the end date is later than the start date. A negative result means the end date is earlier.

If you’re using Microsoft SQL Server, DATEDIFF works out of the box. In MySQL and PostgreSQL, things are slightly different — we’ll come back to that later.

How to Use DATEDIFF in SQL?

Using DATEDIFF is straightforward once you understand the syntax. Let's walk through a few examples using SQL Server to show how it behaves in different situations.

Example 1: Basic Day Difference

SELECT DATEDIFF(day, '2024-01-01', '2024-01-15') AS DayDifference;

Result: 14

This gives the number of days between January 1 and January 15. The result doesn't count the start date itself, just the difference in days.

Example 2: Month Difference

SELECT DATEDIFF(month, '2024-01-01', '2024-03-01') AS MonthDifference;

Result: 2

Even though it's exactly two months, DATEDIFF only considers the difference in the month part — it doesn't care about the day unless you're measuring by day.

Example 3: Negative Result

SELECT DATEDIFF(day, '2024-01-15', '2024-01-01') AS NegativeDifference;

Result: -14

This is because the second date is earlier than the first.

Common Dateparts Used:

• year or yy
• quarter or qq
• month or mm
• day or dd
• hour or hh
• minute or mi
• second or ss

You can also use shorthand like yy for year or dd for day. SQL Server understands both without any issue. These dateparts help control how the difference is calculated.

Practical Use Cases and Behavior

The real usefulness of DATEDIFF shows up when you’re working with logs, deadlines, memberships, or anything that tracks time.

Checking Expired Records

Say you run a library system and want to find which books are overdue:

SELECT book_id, due_date

FROM borrowed_books

WHERE DATEDIFF(day, due_date, GETDATE()) > 0;

This tells you how many days a book is overdue. If the number is greater than 0, the book should’ve been returned already.

Calculating Age

For a user registration system, you may want to know how old someone is:

SELECT name, DATEDIFF(year, birthdate, GETDATE()) AS Age

FROM users;

This gives the difference in years between the birthdate and today. Note that it’s a rough calculation. It doesn’t account for whether the birthday has passed this year.

Comparing Activity Gaps

If you're tracking user logins and want to flag users who haven’t logged in for 30 days:

SELECT user_id, last_login

FROM users

WHERE DATEDIFF(day, last_login, GETDATE()) >= 30;

This is a quick way to find inactive users without doing manual date math.

Grouping Events by Time

Sometimes, you want to find events that happened within the same month:

SELECT COUNT(*), DATEDIFF(month, '2023-01-01', event_date) AS MonthsSinceStart

FROM events

GROUP BY DATEDIFF(month, '2023-01-01', event_date);

This can help visualize trends or activity levels across periods.

Compatibility, Alternatives, and Limitations

Here’s where things can get tricky. DATEDIFF is available in SQL Server, Sybase, and some other T-SQL-based systems. But MySQL and PostgreSQL don’t use the same function name or syntax, and their behaviors vary slightly.

MySQL Alternative

In MySQL, there’s a DATEDIFF function, but it only supports day differences — no dateparts:

SELECT DATEDIFF('2024-01-15', '2024-01-01');

You'll get 14 as a result, but you can't ask for months or years. If you need months, you'll have to write a workaround using TIMESTAMPDIFF:

SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-03-01');

This provides month-level control, making it more versatile for broader date-based calculations.

PostgreSQL Alternative

PostgreSQL doesn’t have DATEDIFF at all. You just subtract the dates directly:

SELECT '2024-01-15'::date - '2024-01-01'::date;

That returns an interval or integer, depending on how you cast it. To get months or years, you’d use date functions like AGE() or manipulate with EXTRACT.

Time Portion Is Ignored

One subtle thing to remember — DATEDIFF usually ignores the actual time of day. So if two timestamps are just hours apart but fall on different calendar days, the day difference will still be 1:

SELECT DATEDIFF(day, '2024-01-01 23:59:59', '2024-01-02 00:00:01');

Result: 1 — even though it’s just two seconds apart. This can cause confusion if time precision matters.

Conclusion

The SQL DATEDIFF function simplifies working with date intervals by calculating the difference between two dates in units like days, months, or years. It's a straightforward tool that saves time, especially when tracking overdue items, user inactivity, or age. SQL Server supports full flexibility with dateparts, while MySQL and PostgreSQL require slightly different approaches. DATEDIFF ignores the time portion and focuses on the specified unit, making it reliable for general date gap tasks. Though it behaves a bit differently across systems, the core idea stays the same—measure time between events easily. Understanding how it works helps keep queries clean and avoids manual date math.