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 = 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'