Get Age

Use a function to calculate someones age at any point of time.

Usage

For some reason we seem to end up on a huge amount of projects where dates are compared. During this time we have developed a number of functions to speed up and ensure the integrity of this data.

The reason we created this was that working out an age where someone was born in a leap year could end up with a variance of one day. In a leap year, 29/02 is the same day of year as 01/03.

We use two dates, as it enables you to backdate the time that you required the age, (like a date of death), and only default in the current date if left blank.

As we don't believe in re-inventing the wheel, we have left our code here for anyone that may find it useful. This requires a secondary function linked below.

More: Get Leap Year

Get Age

CREATE FUNCTION Dates.GetAge(@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGIN
IF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())
DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)
+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>
(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))
THEN -1 ELSE 0 END)
RETURN @Age
END
GO

Test

SELECT Dates.GetAge('2011-02-28','2013-02-27'),Dates.GetAge('2011-02-28','2013-02-28'),Dates.GetAge('2011-02-28','2013-03-01'),Dates.GetAge('2011-02-28','2013-03-02') SELECT Dates.GetAge('2011-02-28','2012-02-27'),Dates.GetAge('2011-02-28','2012-02-28'),Dates.GetAge('2011-02-28','2012-02-29'),Dates.GetAge('2011-02-28','2012-03-01') SELECT Dates.GetAge('2012-02-28','2013-02-27'),Dates.GetAge('2012-02-28','2013-02-28'),Dates.GetAge('2012-02-28','2013-03-01'),Dates.GetAge('2012-02-28','2013-03-02') SELECT Dates.GetAge('2012-02-29','2013-02-27'),Dates.GetAge('2012-02-29','2013-02-28'),Dates.GetAge('2012-02-29','2013-03-01'),Dates.GetAge('2012-02-29','2013-03-02')

Results

Testing so far has not revealed any anomalies, however please let us know if you find any.