Why DATEDIFF Gives the Wrong Age in SQL

Two dates, three days apart, one year apart

SELECT DATEDIFF('day', DATE '2019-12-30', DATE '2020-01-02') AS days_apart,
       DATEDIFF('year', DATE '2019-12-30', DATE '2020-01-02') AS years_apart;

Three days pass. days_apart says 3, which is right. years_apart says 1, which looks wrong, since nobody would call three days "a year." Run it and you'll see both numbers are exactly what DATEDIFF is supposed to return.

DATEDIFF doesn't measure elapsed duration. It counts how many times the calendar boundary for that unit (January 1st for years, the 1st of the month for months) gets crossed between the two dates. December 30 and January 2 sit on opposite sides of exactly one January 1st, so the year count is 1, no matter how few days separate them. This is the part every DATEDIFF tutorial explains for days and silently skips for months and years, and it's the reason "years employed" and "age" columns computed with DATEDIFF are quietly wrong for a chunk of your rows.

Same distance, different unit, opposite answer

The boundary-crossing rule produces a result that looks backwards if you haven't seen it before: a one-day gap can report a bigger month difference than a thirty-day gap.

one_day_gap is 1: January 31 and February 1 are a single day apart, but they straddle the February 1st boundary. thirty_day_gap is 0: January 1 and January 31 are nearly a full month apart, but neither date has crossed into February, so no boundary was crossed. If your mental model is "DATEDIFF tells me how much time passed," these two results contradict each other. If your mental model is "DATEDIFF counts boundary crossings," they're both exactly correct.

Where this actually bites: tenure and age

This isn't just a curiosity with hand-picked dates. It shows up the moment you compute tenure from a hire date, because roughly half the rows in any real table will have a hire month-day that falls on the "wrong" side of today's date.

Pin the reference date at June 15, 2024, and look at Paul, hired July 11, 2017: years_naive reports 7. But his next work anniversary is July 11, 2024, nearly a month after June 15. He has completed 6 full years, not 7. Carla, hired November 20, 2018, gets the same treatment: years_naive says 6, but her sixth anniversary doesn't land until November 20, 2024, so she's only 5 years in. Alice, Brian, and David, hired in March, June, and January, come out correct, because their hire month-day already fell before June 15, so their anniversary this year has already passed. The query is silently right for some rows and silently wrong for others, which is exactly why this bug survives code review: a spot check on the wrong row looks fine.

The fix: subtract 1 when the anniversary hasn't happened yet

The correction is a single CASE expression: compare the month and day of the reference date against the month and day of the start date, and knock one year off if the anniversary hasn't occurred yet this year.

STRFTIME(date, '%m%d') turns each date into a zero-padded four-character string like '0711', which compares correctly as text precisely because it's zero-padded: '0615' < '0711' behaves the same as the numeric comparison would. When the reference date's month-day is earlier than the start date's month-day, this year's anniversary is still ahead, so the CASE subtracts one. Paul and Carla drop to 6 and 5. Alice, Brian, and David, whose anniversaries had already passed, are untouched, because the naive count was already right for them. That's the same logic behind AGE() in Postgres and TIMESTAMPDIFF in MySQL when used with a YEAR unit; if your engine has one of those, prefer it over hand-rolling the CASE. If it doesn't, this is what it's doing internally.

The basics, quickly

DATEDIFF takes the unit first, then the earlier date, then the later date: DATEDIFF(unit, start, end). Swap start and end and the sign flips. For the full syntax, the supported units, and other common mistakes like unit-name spelling across engines, see the DATEDIFF reference page. The date and time functions lesson covers EXTRACT, DATE_TRUNC, and interval arithmetic, which is often a cleaner tool than DATEDIFF when you need to group rows by calendar period rather than count a difference. If you want to group orders or hires by month instead of diffing two specific dates, the GROUP BY with dates example is the pattern for that.

Where to go next

Any query with "years of service," "age," "days until renewal," or "quarters since signup" in it is worth re-checking against this rule. Run it for one row where the anniversary has passed and one where it hasn't. if the two behave differently, you've found the same bug this post walked through. The practice problems are a good place to try writing the corrected version yourself before you need it in production.