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:

  1. Public Function Years( _
  2. ByVal datDate1 As Date, _
  3. ByVal datDate2 As Date) _
  4. As Integer
  5.  
  6. ' Returns the difference in full years between datDate1 and datDate2.
  7.  
  8. ' ' Calculates correctly for:
  9. ' negative differences
  10. ' leap years
  11. ' dates of February 29
  12. ' date/time values with embedded time values
  13. ' negative date/time values (prior to 1899-12-29)
  14.  
  15. ' ' 2000-11-03. Cactus Data ApS, CPH.
  16. ' 2000-12-16. Leap year correction modified to be symmetrical.
  17. ' Calculation of intDaysDiff simplified.
  18. ' Renamed from YearsDiff() to Years().
  19. ' 2000-12-18. Introduced cbytMonthDaysMax.
  20.  
  21. ' Constants for leap year calculation. Last normal date of February.
  22. Const cbytFebMonth As Byte = 2
  23. Const cbytFebLastDay As Byte = 28
  24. ' Maximum number of days in a month.
  25. Const cbytMonthDaysMax As Byte = 31
  26.  
  27. Dim intYears As Integer
  28. Dim intDaysDiff As Integer
  29. Dim intReversed As Integer
  30.  
  31. ' No special error handling.
  32. On Error Resume Next
  33.  
  34. intYears = DateDiff("yyyy", datDate1, datDate2)
  35. If intYears = 0 Then
  36. ' Both dates fall within the same year.
  37. Else
  38. ' Check for ultimo February and leap years.
  39. If (Month(datDate1) = cbytFebMonth) And (Month(datDate2) = cbytFebMonth) Then
  40. ' Both dates fall in February.
  41. ' Check if dates are at ultimo February.
  42. If (Day(datDate1) >= cbytFebLastDay) And (Day(datDate2) >= cbytFebLastDay) Then
  43. ' Both dates are at ultimo February.
  44. ' Check if the dates fall in leap years.
  45. If Day(DateSerial(Year(datDate1), cbytFebMonth + 1, 0)) = cbytFebLastDay Xor _
  46. Day(DateSerial(Year(datDate2), cbytFebMonth + 1, 0)) = cbytFebLastDay Then
  47. ' Only one date falls within a leap year.
  48. ' Adjust both dates to day 28 of February.
  49. datDate1 = DateAdd("d", cbytFebLastDay - Day(datDate1), datDate1)
  50. datDate2 = DateAdd("d", cbytFebLastDay - Day(datDate2), datDate2)
  51. Else
  52. ' Both dates fall either in leap years or non leap years.
  53. ' No adjustment needed.
  54. End If
  55. End If
  56. End If
  57. ' Calculate day difference using months and days as Days() will fail when
  58. ' comparing leap years with non leap years for dates after February.
  59. intDaysDiff = (Month(datDate1) * cbytMonthDaysMax + Day(datDate1)) - (Month(datDate2) * cbytMonthDaysMax + Day(datDate2))
  60. intReversed = Sgn(intYears)
  61. ' Decrease count of years by one if dates are closer than one year.
  62. intYears = intYears + (intReversed * ((intReversed * intDaysDiff) > 0))
  63. End If
  64.  
  65. Years = intYears
  66.  
  67. End Function
  68.  
  69. Public Function Age( _
  70. ByVal datDateOfBirth As Date, _
  71. Optional ByVal varDate As Variant) _
  72. As Integer
  73.  
  74. ' Calculates age at today's date or at a specified date earlier or later in time.
  75. ' Uses Years() for calculating difference in years.
  76.  
  77. ' ' 2000-11-03. Cactus Data ApS, CPH.
  78.  
  79. Dim datDate As Date
  80.  
  81. ' No special error handling.
  82. On Error Resume Next
  83.  
  84. If IsDate(varDate) Then
  85. datDate = CDate(varDate)
  86. Else
  87. datDate = Date
  88. End If
  89.  
  90. Age = Years(datDateOfBirth, datDate)
  91.  
  92. 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:

  1. DECLARE
  2. @birthdate AS DATETIME,
  3. @eventdate AS DATETIME;
  4.  
  5. SET @birthdate = '20040229';
  6. SET @eventdate = '20070227';
  7.  
  8. SELECT
  9. DATEDIFF(year, @birthdate, @eventdate)
  10. - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)
  11. THEN 1 ELSE 0
  12. END AS Age
  13.  
  14. -- Output 2 for @eventdate = '20070227'
  15. -- Output 2 for @eventdate = '20070228'
  16. -- Output 3 for @eventdate = '20070301'
  17. -- Output 3 for @eventdate = '20080228'
  18. -- 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:

  1. DECLARE
  2. @birthdate AS DATETIME,
  3. @eventdate AS DATETIME;
  4.  
  5. SET @birthdate = '20040229';
  6. SET @eventdate = '20070227';
  7.  
  8. SELECT
  9. DATEDIFF(year, @birthdate, @eventdate)
  10. - CASE WHEN DATEADD(year, DATEDIFF(year, @birthdate, @eventdate), @birthdate) > @eventdate
  11. THEN 1 ELSE 0
  12. END AS Age
  13.  
  14. -- Output 2 for @eventdate = '20070227'
  15. -- Output 3 for @eventdate = '20070228'
  16. -- Output 3 for @eventdate = '20070301'
  17. -- Output 3 for @eventdate = '20080228'
  18. -- Output 4 for @eventdate = '20080229'