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.

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

Calculate Age in SQL

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

Your email address is not shown. A photo comes from Gravatar, if you have one.

  1. Howard Rothenburg

    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

  2. Howard Rothenburg

    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

  3. Gert-Jan

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