使用DATEDIFF筛选入职满2年员工的SQL语句异常问题排查

Ah, I get it—this is a super common pitfall with SQL Server's DATEDIFF(YEAR) function! Let me break down what's happening here.

The Problem with DATEDIFF(YEAR) #

The DATEDIFF(YEAR, startdate, enddate) function doesn't calculate the actual number of full years between two dates. Instead, it only looks at the difference between the year parts of the two dates.

For example:

• If today is 2019-02-09, DATEDIFF(YEAR, '2017-02-10', GETDATE()) returns 2 because 2019 - 2017 = 2. But in reality, the employee hasn't hit their 2-year anniversary yet (that's on 2019-02-10). Your query is counting them as "employed for 2 years" a day early.
• When the hire date was 2017-02-09, it worked because the anniversary falls exactly on today—so the year difference is 2, and the actual time passed is indeed 2 full years.

The Fix: Compare Full Date Periods #

To accurately filter employees who have been employed for at least 2 full years, you need to check if their hire date is on or before the date exactly 2 years ago today. Here's how to do it:

SELECT Date_of_employment, FirstName, LastName 
FROM MemInfo 
WHERE Date_of_Employment <= DATEADD(YEAR, -2, GETDATE())

Or, if you prefer to think in terms of "adding 2 years to the hire date and checking if that's in the past":

SELECT Date_of_employment, FirstName, LastName 
FROM MemInfo 
WHERE DATEADD(YEAR, 2, Date_of_Employment) <= GETDATE()

Both versions work, but the second one might be more intuitive for some—it directly checks if the 2-year mark has already passed.

Why This Works #

These queries account for the full date (year, month, and day) instead of just the year. Using our earlier example:

• For a hire date of 2017-02-10, DATEADD(YEAR, 2, '2017-02-10') gives 2019-02-10. If today is 2019-02-09, this date is in the future, so the employee isn't included—exactly what you want.
• For 2017-02-09, DATEADD(YEAR, 2, '2017-02-09') is 2019-02-09, which equals today, so they are included correctly.

Bonus: Handling Time Components (If Applicable) #

If your Date_of_Employment column includes time (e.g., 2017-02-10 14:30:00), the second query is even more precise—it will only include the employee once the exact time of their hire has passed 2 years later.

内容的提问来源于stack exchange，提问作者Brian Angelo Cidro