In response to Itzik Ben-Gan's "DATETIME Calculations, Part 5," reader Gustav Brock commended Itzik for providing such a comprehensive look at a single topic. "That way," he said, "you can reach some corners and not only the broad lines."

Related: Age Calculations

"However," Brock went on, "Black Belt suggests something such as "optimum" or "superior." Thus, I was a little disappointed to see that you published a not-so-good old and limited method for calculating age. This solution is limited because it fails for users born on February 29 when age is calculated for February 28 in a common (non-leap) year. The rule is that for such years, February 28 is used as substitute for the missing February 29."

Brock admits to not writing much T-SQL, but he does have a function for Access VBA that he maintains "gets it right." Here it is:

                              Public Function Years( _                              ByVal datDate1 As Date, _                              ByVal datDate2 As Date) _                              As Integer                              ' Returns the difference in full years between datDate1 and datDate2.                              ' ' Calculates correctly for:                              ' negative differences                              ' leap years                              ' dates of February 29                              ' date/time values with embedded time values                              ' negative date/time values (prior to 1899-12-29)                              ' ' 2000-11-03. Cactus Data ApS, CPH.                              ' 2000-12-16. Leap year correction modified to be symmetrical.                              ' Calculation of intDaysDiff simplified.                              ' Renamed from YearsDiff() to Years().                              ' 2000-12-18. Introduced cbytMonthDaysMax.                              ' Constants for leap year calculation. Last normal date of February.                              Const cbytFebMonth As Byte = 2                              Const cbytFebLastDay As Byte = 28                              ' Maximum number of days in a month.                              Const cbytMonthDaysMax As Byte = 31                              Dim intYears As Integer                              Dim intDaysDiff As Integer                              Dim intReversed As Integer                              ' No special error handling.                              On Error Resume Next                              intYears = DateDiff("yyyy", datDate1, datDate2)                              If intYears = 0 Then                              ' Both dates fall within the same year.                              Else                              ' Check for ultimo February and leap years.                              If (Month(datDate1) = cbytFebMonth) And (Month(datDate2) = cbytFebMonth) Then                              ' Both dates fall in February.                              ' Check if dates are at ultimo February.                              If (Day(datDate1) >= cbytFebLastDay) And (Day(datDate2) >= cbytFebLastDay) Then                              ' Both dates are at ultimo February.                              ' Check if the dates fall in leap years.                              If Day(DateSerial(Year(datDate1), cbytFebMonth + 1, 0)) = cbytFebLastDay Xor _                              Day(DateSerial(Year(datDate2), cbytFebMonth + 1, 0)) = cbytFebLastDay Then                              ' Only one date falls within a leap year.                              ' Adjust both dates to day 28 of February.                              datDate1 = DateAdd("d", cbytFebLastDay - Day(datDate1), datDate1)                              datDate2 = DateAdd("d", cbytFebLastDay - Day(datDate2), datDate2)                              Else                              ' Both dates fall either in leap years or non leap years.                              ' No adjustment needed.                              End If                              End If                              End If                              ' Calculate day difference using months and days as Days() will fail when                              ' comparing leap years with non leap years for dates after February.                              intDaysDiff = (Month(datDate1) * cbytMonthDaysMax + Day(datDate1)) - (Month(datDate2) * cbytMonthDaysMax + Day(datDate2))                              intReversed = Sgn(intYears)                              ' Decrease count of years by one if dates are closer than one year.                              intYears = intYears + (intReversed * ((intReversed * intDaysDiff) > 0))                              End If                              Years = intYears                              End Function                              Public Function Age( _                              ByVal datDateOfBirth As Date, _                              Optional ByVal varDate As Variant) _                              As Integer                              ' Calculates age at today's date or at a specified date earlier or later in time.                              ' Uses Years() for calculating difference in years.                              ' ' 2000-11-03. Cactus Data ApS, CPH.                              Dim datDate As Date                              ' No special error handling.                              On Error Resume Next                              If IsDate(varDate) Then                              datDate = CDate(varDate)                              Else                              datDate = Date                              End If                              Age = Years(datDateOfBirth, datDate)                              End Function                              

In response, Itzik says, "Different systems have different rules, and in fact, for many legal purposes, the age of a person born on February 29 in a leap year changes in a non-leap year on March 1--not February 28. Hence, my choice of which rule to apply. The code I provided purposely and intentionally adheres to this rule:

                              DECLARE                              @birthdate AS DATETIME,                              @eventdate AS DATETIME;                              SET @birthdate = '20040229';                              SET @eventdate = '20070227';                              SELECT                              DATEDIFF(year, @birthdate, @eventdate)                              - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)                              THEN 1 ELSE 0                              END AS Age                              -- Output 2 for @eventdate = '20070227'                              -- Output 2 for @eventdate = '20070228'                              -- Output 3 for @eventdate = '20070301'                              -- Output 3 for @eventdate = '20080228'                              -- Output 4 for @eventdate = '20080229'                              

If you're working with a system in which the age of a person born on February 29 in a leap year is supposed to change in a non-leap year on February 28, it would have actually been too easy to calculate with T-SQL; that’s because the T-SQL DATEADD function generates a February 28 date in a non-leap year when you add whole years to a February 29 date! Here’s how the calculation would have looked like:

                              DECLARE                              @birthdate AS DATETIME,                              @eventdate AS DATETIME;                              SET @birthdate = '20040229';                              SET @eventdate = '20070227';                              SELECT                              DATEDIFF(year, @birthdate, @eventdate)                              - CASE WHEN DATEADD(year, DATEDIFF(year, @birthdate, @eventdate), @birthdate) > @eventdate                              THEN 1 ELSE 0                              END AS Age                              -- Output 2 for @eventdate = '20070227'                              -- Output 3 for @eventdate = '20070228'                              -- Output 3 for @eventdate = '20070301'                              -- Output 3 for @eventdate = '20080228'                              -- Output 4 for @eventdate = '20080229'