Share via

Verifiable bug in DateDiff

Gregory Hart 20 Reputation points
2025-11-22T21:50:46.0166667+00:00

I found a verifiable bug in DateDiff while working to perfect an easy Age formula based on birthdate.

 The first three SQL return results as expect:

·         SELECT Convert(Int, DateDiff(Minute, '02/29/2000', '03/01/2096') / (60.0 * 24.0 * 365.25) );

·         SELECT Convert(Int, DateDiff(Minute, '02/29/2000', '03/01/2100') / (60.0 * 24.0 * 365.25) );

·         SELECT Convert(Int, DateDiff(Minute, '02/29/2000', '03/01/2196') / (60.0 * 24.0 * 365.25) );

 This fourth one returns an invalid result (199 years old).

·         SELECT Convert(Int, DateDiff(Minute, '02/29/2000', '03/01/2200') / (60.0 * 24.0 * 365.25) );

This is because the leap year in 2200 is skipped, but your DateDiff calculation does not account for this. Further complication of leap years is on 2000 which was a skip of the skip of centennial leap years, so it was NOT skipped.

 

SQL Server | SQL Server Transact-SQL
Answer accepted by question author
  1. Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator
    2025-11-23T20:40:34.8+00:00

    I don't think it is a bug in datediff. But let's check.

    For year 2000 we have 366-31-29+1= 307 days.

    For year 2200 we have 31+28 = 59 days.

    In between we have 199 years. Of these 48 are leap years.

    This gives a total of 307 + 59 + 199*365 + 48 = 73049

    Multiply by 1440 and we get 105190560.

    Which is precisely what SELECT DateDiff(Minute, '02/29/2000', '03/01/2200') returns. So I cannot see any bug there.

    Then again, what you thought was wrong was the result of DateDiff * 60 * 24 * 365.25, and the last value is the wrong one. 365.25 works with the old Julian calendar which had leap years every four years, including years like 2100 and 2200. But as we know, the length of a year is slightly shorter than 365.25 days.

    The multiplication with 365.25 works with shorter intervals that do not cross years 2100 and 2200. To cover these, you need something more sophisticated. (A plain factor won't do, since it is irrelegular.)

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-11-22T21:50:54.35+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    The issue you're experiencing with the DateDiff function in SQL Server regarding leap years is indeed a known complexity in date calculations. The DateDiff function calculates the difference between two dates based on the specified date part (in your case, minutes), but it does not inherently account for the rules governing leap years, particularly the exceptions for centennial years.

    In the Gregorian calendar, a year is a leap year if:

    • It is divisible by 4.
    • However, if the year is divisible by 100, it is not a leap year unless it is also divisible by 400.

    This means that while 2000 was a leap year, 2100 will not be, and similarly, 2200 will also not be a leap year. Therefore, your calculation for the age based on the DateDiff function does not yield the expected results when crossing these boundaries.

    To work around this issue, you might consider implementing a custom function that explicitly checks for leap years when performing date calculations, or you could adjust your calculations to account for these exceptions manually.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.