SQL Code Snippet - Getting Accurate Years Elapsed Between Two Dates

Sometimes when coding it is important to be more granular than the DATEDIFF functionality allows for in SQL server.  In SQL server if you try to compare two dates, and those two dates happen on two different years, the "Years" between the dates will always be (Later Year - Earlier Year) depending on the order you use of course.

This can be bad sometimes.  For example, if you are trying to see if someone is 32 years old and is eligible for your special program or not, using the DATEDIFF functionality simply will not cut it, especially when the dates themselves are within days of the value. (IE trying to use 365 days as a rule of thumb doesn't work because of leap days).

Here is a method I wrote to handle this edge case, with comments.

DECLARE @BIRTHDATE datetime

DECLARE @DATE1 datetime

DECLARE @DATE2 datetime

SET @DATE1 = '02/02/2012'

SET @DATE2  = '01/30/2012'

SET @BIRTHDATE = '02/01/1980'

/*

The following shows how SQL server fails when determining the years between two dates.

*/

SELECT

 DATEDIFF(YYYY,@BIRTHDATE,@DATE1) as datediffabs   -- Should be 32, correct.

, DATEDIFF(YYYY,@BIRTHDATE,@DATE2) as datediffabs2 -- Should be 31 + 364 days

/*

Using the above, the following code step by step shows using both above examples and correctly determining the number of years

between the two dates.

*/

SELECT

 DATEDIFF(YYYY,@BIRTHDATE,@DATE1) as DATE1diff  -- The raw difference returned by SQL for the first claim date vs the birthdate

, DATEDIFF(YYYY,@BIRTHDATE,@DATE2) as Date2Diff -- The raw difference returned by SQL for the second claim date vs the birthdate

, case when MONTH(@DATE1) > MONTH(@BIRTHDATE) OR ( MONTH(@DATE1) = MONTH(@BIRTHDATE)  and DAY(@DATE1) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as DATE1Modifier  -- This is the underlying modifier being used for Date 1 to determine if it exceeds the birthdate

, case when MONTH(@DATE2) > MONTH(@BIRTHDATE) OR (  MONTH(@DATE2) = MONTH(@BIRTHDATE) and DAY(@DATE2) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as Date2Modifier  -- This is the underlying modifier being used for Date 2 to determine if it exceeds the birthdate

, DATEDIFF(YYYY,@BIRTHDATE,@DATE1) - case when MONTH(@DATE1) > MONTH(@BIRTHDATE) OR ( MONTH(@DATE1) = MONTH(@BIRTHDATE)  and DAY(@DATE1) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as ModifiedDATE1 -- The modified years since birth date for the first claim date

, DATEDIFF(YYYY,@BIRTHDATE,@DATE2) - case when MONTH(@DATE2) > MONTH(@BIRTHDATE) OR (  MONTH(@DATE2) = MONTH(@BIRTHDATE) and DAY(@DATE2) >= DAY(@BIRTHDATE ) )

      then 0 else 1 end as ModifiedDate2  -- The modified years since birth date for the second claim date