SQL Server Magazine

In response to Itzik Ben-Gan's "DATETIME Calculations, Part 5" (InstantDoc ID 95675), 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."

"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)
< 100 * MONTH(@birthdate) + DAY(@birthdate)
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'

End of Article