cfox 61877 (1/23/2015)
I find this works better than a scalar function...
CREATE FUNCTION dbo.AgeInYears
(
@BirthDate DATETIME2,
@AsOfDate DATETIME2
)
RETURNS TABLE
AS
RETURN
(
SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -
CASE WHEN DATEPART(DY, @AsOfDate) < DATEPART(DY, @BirthDate) THEN 1
ELSE 0
END AS Age
);
Then a simple CROSS APPLY in the query...
SELECT I.Name, I.Birthdate, A.Age
FROM Individual AS I
CROSS APPLY AgeInYears(I.Birthdate, CURRENT_TIMESTAMP) AS A
WHERE A.Age >= 18;
seems to be a day out in leap years e.g. @BirthDate = '2001/04/01' , @AsOfDate = '2016/03/31' gives 15 and it should still be 14