Get Age
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.
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(@Until)=1 AND DATEPART(DAYOFYEAR,@Until)>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.
