Home / T-SQL / Calculate Age in SQL Server

Calculate Age in SQL Server

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

Calculate Age in SQL

Check Also

Postcoderange opsplitsen naar losse records

3 comments

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

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *