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 BEGINIF @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(@Until)=1 AND DATEPART(DAYOFYEAR,@Until)>59 THEN -1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDGO

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.