sqlsql-servert-sql

How to calculate age (in years) based on Date of Birth and getDate()


I have a table listing people along with their date of birth (currently a nvarchar(25))

How can I convert that to a date, and then calculate their age in years?

My data looks as follows

ID    Name   DOB
1     John   1992-01-09 00:00:00
2     Sally  1959-05-20 00:00:00

I would like to see:

ID    Name   AGE  DOB
1     John   17   1992-01-09 00:00:00
2     Sally  50   1959-05-20 00:00:00

Solution

  • There are issues with leap year/days and the following method, see the update below:

    try this:

    DECLARE @dob  datetime
    SET @dob='1992-01-09 00:00:00'
    
    SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
        ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
        ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc
    

    OUTPUT:

    AgeYearsDecimal                         AgeYearsIntRound AgeYearsIntTrunc
    --------------------------------------- ---------------- ----------------
    17.767054                               18               17
    
    (1 row(s) affected)
    

    UPDATE here are some more accurate methods:

    BEST METHOD FOR YEARS IN INT

    DECLARE @Now  datetime, @Dob datetime
    SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
    --SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
    --SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
    --SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
    --SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
    --SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10
    
    SELECT
        (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears
    

    you can change the above 10000 to 10000.0 and get decimals, but it will not be as accurate as the method below.

    BEST METHOD FOR YEARS IN DECIMAL

    DECLARE @Now  datetime, @Dob datetime
    SELECT   @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
    --SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in  9.997260273973
    --SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in  9.002739726027
    --SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
    --SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
    --SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973
    
    SELECT 1.0* DateDiff(yy,@Dob,@Now) 
        +CASE 
             WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN  --birthday has happened for the @now year, so add some portion onto the year difference
               (  1.0   --force automatic conversions from int to decimal
                  * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
                  / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
               )
             ELSE  --birthday has not been reached for the last year, so remove some portion of the year difference
               -1 --remove this fractional difference onto the age
               * (  -1.0   --force automatic conversions from int to decimal
                    * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
                    / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
                 )
         END AS AgeYearsDecimal