Calculating Leap Year

Last Post 03 Jun 2007 11:32 PM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

01 Jun 2007 07:55 AM
The following exchange between Itizik and a reader was prompted by the June 2007 T-SQL Blackbelt article on DATETIME Calculations. DATETIME Calculations, Part 5 deals with calculating a person's next birthday when it falls on a leap year.

However, "black belt" indicates to me something like "optimum" or "superior". Thus I was a little disappointed to see one of the not-so-good old and limited method for calculating age being published again. This one is limited as it will fail for those born on 29. Feb. when age is calculated for 28. Feb. in a common (non-leap) year. The rule here is, that for such years 28. Feb. is used as substitute for the missing 29. Feb.

I don't write that much T-SQL but I have a function for Access VBA that does it right:


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 29. February
' 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.
' 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)
' 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
New Member
New Member

03 Jun 2007 11:32 PM
SELECT @Year, ISDATE(CAST(@Year AS VARCHAR) + '0229') AS LeapYear
You are not authorized to post a reply.

Acceptable Use Policy