I know this question is old but I have an Inline Table Valued Function that calculates the age quite well.
The way it works is to calculate the difference between the
in months. Then it checks the day of
to see if it is greater than the day of
, if it is then subtract 1 from the month calculation because the person's birthday has not arrived yet. Otherwise, subtract 0.
By writing this as an Inline Table Valued Function rather than a Scalar function the query that it is used in will run very fast.
You can call it for a single calculation by:
SELECT AgeInYears FROM dbo.CalculateAgeInYears('1980-07-01', GETDATE())
Or you can use CROSS APPLY to calculate from a table:
FROM #SampleData AS t
CROSS APPLY dbo.CalculateAgeInYears(t.DOB, GETDATE()) AS fn
CREATE FUNCTION [dbo].[CalculateAgeInYears]
SELECT (DATEDIFF(MONTH, @StartDate, @EndDate) - CASE WHEN DATEPART(DAY, @StartDate) > DATEPART(DAY, @EndDate) THEN 1 ELSE 0 END) / 12 AS AgeInYears