SQLBlog Nederland Business Intelligence & Datawarehousing
In my daily work, I need to calculate and report the age of people very often. There are a couple of ways to calculate the age and will give you the right result. I do not recommend a SQL Function to to this. SQL Functions are performing very slow, especially on large datasets. With some easy to implement SQL code, the age will appear on your screen. Most of the time, you will Calculate Age without decimals but I have included an example with decimals too. Just like converting a datetime to an int, use plain SQL and no functions!
How to Calculate Age in T-SQL?
Below you will find some manners to calculate the age of a person in T-SQL (SQL SERVER). All calculations will give you the same result. I don’t know if one is faster or slower.
DECLARE @DateofBirth datetime = CONVERT(datetime, '2/29/1948') DECLARE @ReportStartDate datetime = CONVERT(datetime, '1/1/2014') SELECT FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8766) SELECT DATEDIFF(HOUR,@DateofBirth,@ReportStartDate)/8766.0 AS AgeYearsDecimal ,DATEDIFF(HOUR,@DateofBirth,@ReportStartDate)/8766 AS AgeYearsIntTrunc SELECT (CASE WHEN DATEADD(YEAR, datediff(YEAR, @DateofBirth, @ReportStartDate), @DateofBirth) > @ReportStartDate THEN DATEDIFF (YEAR, @DateofBirth, @ReportStartDate) - 1 ELSE DATEDIFF (YEAR, @DateofBirth, @ReportStartDate) END) AS Age SELECT FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) SELECT FLOOR(DATEDIFF(YEAR,@DateofBirth,@ReportStartDate) - (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DateofBirth,@ReportStartDate),@DateofBirth) > @ReportStartDate THEN 1 ELSE 0 END )) |
CREATE FUNCTION [dbo].[ufn_GetAge]
(@pDateOfBirth DATETIME,
@pAsOfDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @vAge INT;
IF @pDateOfBirth >= @pAsOfDate
RETURN 0;
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate);
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate)
OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate)
AND DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge – 1;
RETURN @vAge;
END;
GO
Select DATEDIFF(yy, patient.BirthDate, GETDATE()) – CASE
WHEN MONTH(patient.BirthDate) > MONTH(GETDATE())
OR (MONTH(patient.BirthDate) = MONTH(GETDATE())
AND DAY(patient.BirthDate) > DAY(GETDATE()))
THEN 1
ELSE 0
END AS Age
There is no solution based that will always return correct results based on one DATEDIFF.
For example, the formula DATEDIFF(HOUR,@DateofBirth,@ReportStartDate)/8766 return an incorrect result for date of birth 19 January 1994 and probe date 19 January 2016.
The formula FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) also fails for on a combination like that.
The other formula’s are fine, except that the FLOOR() function in the last formula doesn’t add any value (since it acts on integer subtraction).