The Right Way to Calculate a Leap-Year Birthday?

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 = 2Const cbytFebLastDay As Byte = 28' Maximum number of days in a month.Const cbytMonthDaysMax As Byte = 31Dim intYears As IntegerDim intDaysDiff As IntegerDim intReversed As Integer' No special error handling.On Error Resume NextintYears = 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 IfEnd IfEnd 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 IfYears = intYearsEnd FunctionPublic 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 NextIf IsDate(varDate) ThendatDate = CDate(varDate)ElsedatDate = DateEnd IfAge = 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';SELECTDATEDIFF(year, @birthdate, @eventdate)- CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)THEN 1 ELSE 0END 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';SELECTDATEDIFF(year, @birthdate, @eventdate)- CASE WHEN DATEADD(year, DATEDIFF(year, @birthdate, @eventdate), @birthdate) > @eventdateTHEN 1 ELSE 0END 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'