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
@StartTime
and
@EndTime
in months. Then it checks the day of
@StartTime
to see if it is greater than the day of
@EndTime
, 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:
SELECT *
FROM #SampleData AS t
CROSS APPLY dbo.CalculateAgeInYears(t.DOB, GETDATE()) AS fn
CREATE FUNCTION [dbo].[CalculateAgeInYears]
(
@StartDate DATETIME,
@EndDATE DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT (DATEDIFF(MONTH, @StartDate, @EndDate) - CASE WHEN DATEPART(DAY, @StartDate) > DATEPART(DAY, @EndDate) THEN 1 ELSE 0 END) / 12 AS AgeInYears
)