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 ))
