One common task is to determine someone's age, although this logic can be applied to various tasks, including anniversaries, or duration until some future date.
The below example is a T-Sql user-defined function for SQL Server 2000 or 2005, but can easily be converted to any programming language.
create function [dbo].[GetAge](
@BirthDate datetime,
@AsOfDate datetime
)
returns decimal(12,6)
as
begin
declare @Years int,
@BirthDateYear int,
@BirthDateMonth int,
@BirthDateDay int,
@AsOfDateYear int,
@AsOfDateMonth int,
@AsOfDateDay int,
@AnniversaryYear int,
@Age decimal(12,6),
@Divisor decimal(12,6),
@Anniversary datetime,
@LeapYearDay datetime
set @BirthDateYear = year(@BirthDate)
set @BirthDateMonth = month(@BirthDate)
set @BirthDateDay = day(@BirthDate)
set @AsOfDateYear = year(@AsOfDate)
set @AsOfDateMonth = month(@AsOfDate)
set @AsOfDateDay = day(@AsOfDate)
set @Years = @AsOfDateYear - @BirthDateYear
if (@Years > 0)
begin
if (@AsOfDateMonth < @BirthDateMonth)
set @Years = @Years - 1
else if (@AsOfDateMonth = @BirthDateMonth) and (@AsOfDateDay < @BirthDateDay)
set @Years = @Years - 1
end
set @Age = cast(@Years as decimal(12,6))
set @Anniversary = dateadd(yy, @Years, @BirthDate)
if (@Anniversary <> @AsOfDate)
begin
set @AnniversaryYear = year(@Anniversary)
set @LeapYearDay = null
if (@AnniversaryYear % 4 = 0)
set @LeapYearDay = convert(datetime, '2/29/' + convert(varchar(4), @AnniversaryYear))
else if (@AsOfDateYear % 4 = 0)
set @LeapYearDay = convert(datetime, '2/29/' + convert(varchar(4), @AsOfDateYear))
if (@LeapYearDay is not null) and (@LeapYearDay between @Anniversary and @AsOfDate)
set @Divisor = 366.0
else
set @Divisor = 365.0
set @Age = @Age +
cast(round(cast(datediff(dd, @Anniversary, @AsOfDate) as decimal(12,4)) / @Divisor, 4) as decimal(12,6))
end
return @Age
end
This formula accounts for leap years and the time between the "as of" date, and the birth date's "anniversary".
The results
-- Elapsed time since date: 66.34 years
select dbo.[GetAge] ('12/7/1941', '4/8/2008')
-- Time until date: 4.7 years
select dbo.[GetAge] '4/8/2008', 12/21/2012')