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.
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(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDGO
Testing so far has not revealed any anomalies, however please let us know if you find any.