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) > @ReportStartDateTHEN DATEDIFF (YEAR, @DateofBirth, @ReportStartDate) - 1ELSE 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 ))
Frequently asked questions
How do I calculate age in T-SQL?
Subtract the years with DATEDIFF(YEAR, ...) and subtract 1 if the birthday has not passed yet. Use a CASE with DATEADD to check this. Plain SQL gives the right result.
Should I use a SQL function to calculate age?
A SQL function is slow, especially on large datasets. Use plain SQL in the query instead.
How do I calculate age with decimals in SQL Server?
Use DATEDIFF(hour, @DateofBirth, @ReportStartDate)/8766.0 and call it AgeYearsDecimal. Dividing by 8766 without the .0 gives a truncated integer.
How do I calculate age in whole years with DATEDIFF and hours?
Use FLOOR(DATEDIFF(HOUR, @DateofBirth, @ReportStartDate)/8766). A divisor of 8765.82 works as well. Both give the age in years without decimals.
Do all T-SQL age calculations give the same result?
Yes. The DATEDIFF hour variants and the CASE with DATEADD all give the same age. It is not known if one is faster or slower than another.
3 comments
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).
Related blogs
T-TSQL - Create 4 week Moving Total
Calculate Lost Customers using T-SQL
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint