How to Calculate Age in Sql Server

Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:

Script which gives Wrong Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
     GETDATE() 'Current Date',
 DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'

Incorrect Result:

Date of Birth   Current Date                Age in Years
-------------   -----------------------     ------------
1980-12-29      2012-04-26 09:46:21.833     32
(1 row(s) affected)

Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.

To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:

Script which gives Correct Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
   DATEDIFF(YEAR,@DOB,GETDATE())
     -
   (CASE
      WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
            >  GETDATE() THEN 1
      ELSE 0 END)
   'Age in Years'

Correct Result:

Date of Birth   Current Date              Age in Years
-------------   -----------------------   ------------
1980-12-29      2012-04-26 10:23:05.690   31
(1 row(s) affected)

Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.

17 thoughts on “How to Calculate Age in Sql Server

  1. Sql server 2005 code:

    DECLARE @DOB datetime
    set @DOB =’05/01/1982′
    SELECT @DOB ‘Date of Birth’, GETDATE() ‘Current Date’,
    DATEDIFF(YEAR,@DOB,GETDATE())

    (CASE
    WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
    > GETDATE() THEN 1
    ELSE 0 END)
    ‘Age in Years’

  2. What a complicated code. Just do this:
    age=
    floor(
    ( cast(convert(varchar(8),getdate(),112) as int)-
    cast(convert(varchar(8),@dob,112) as int) ) / 10000
    )

      1. –Create table variable to hold our test records
        DECLARE @Workers TABLE (WorkerName VARCHAR(50), DOB DATE);

        –Insert test records
        INSERT INTO @Workers
        SELECT ‘Ryan’, ‘1972-08-24’ UNION ALL
        SELECT ‘James’, ‘1985-09-26’ UNION ALL
        SELECT ‘Jasson’, ‘1983-08-25’ UNION ALL
        SELECT ‘Tara’, ‘1991-09-24’ UNION ALL
        SELECT ‘William’, ‘1992-08-19’ UNION ALL
        SELECT ‘Judy’, ‘1989-09-15’;

        –Variable to provide required number of days
        DECLARE @InNextDays INT;
        SET @InNextDays = 3;

        — Query to find workers, whose birthday is in given number of days
        SELECT *
        FROM @Workers
        WHERE DATEADD( Year, DATEPART( Year, GETDATE()) – DATEPART( Year, DOB), DOB)
        BETWEEN CONVERT( DATE, GETDATE())
        AND CONVERT( DATE, GETDATE() + @InNextDays);

    1. Thank you, I agree that yours is the simplest code. I knew there had to be a way being MS ACCESS can do it with the following code:

      Drv_Age_Dschg: DateDiff(“yyyy”,[dbo_APCReportHeader].[Pat_Dob],[dbo_APCReportHeader].[Claim_Dschg_DT])+Int(Format([dbo_APCReportHeader].[Claim_Dschg_DT],”mmdd”)<Format([dbo_APCReportHeader].[Pat_Dob],"mmdd"))

    1. SELECT
      DOB,
      DATEDIFF(YY,DOB,GETDATE()) AS YEAR,
      DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) AS BIRTHDAY,
      CASE
      WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE()
      THEN DATEDIFF(YY,DOB,GETDATE()) -1
      ELSE DATEDIFF(YY,DOB,GETDATE())
      END as age
      FROM WORKERS

      1. SELECT
        DOB,
        DATEDIFF(YY,DOB,GETDATE()) AS YEAR,
        DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) AS BIRTHDAY,
        CASE
        WHEN DATEADD(YY,DATEDIFF(YY,DOB,GETDATE()),DOB) > GETDATE()
        THEN DATEDIFF(YY,DOB,GETDATE()) -1
        ELSE DATEDIFF(YY,DOB,GETDATE())
        END as age
        FROM WORKERS

  3. sir I want to make a function in which I pas a date and the function will calculate difference between passes date and getdate.
    but not only year I want to year , month and days
    like this
    **years,**months,**days

    1. declare @date_of_birth as datetime=’2015-12-31 10:00:01′;
      Select cast((DATEDIFF(m, @date_of_birth, GETDATE())/12) as varchar) + ‘ Y & ‘ +
      cast((DATEDIFF(m, @date_of_birth, GETDATE())%12) as varchar) + ‘ M & ‘ +
      –cast((DATEDIFF(m, @date_of_birth, GETDATE())%31) as varchar) + ‘ D ‘
      cast((datepart(d,GETDATE())-datepart(d,@date_of_birth)) as varchar) + ‘D’
      as Age

  4. DECLARE @Test TABLE (
    EmpName VARCHAR(40) ,
    BirthDate DATE
    );

    INSERT INTO @Test ( EmpName , BirthDate
    )
    VALUES ( ’30 Yrs old yesterday’ , DATEADD(day , -1 , DATEADD(year , -30 , GETDATE()))
    ) , ( ’30 Yrs old today’ , DATEADD(year , -30 , GETDATE())
    ) , ( ’30 Yrs old tomorrow’ , DATEADD(day , 1 , DATEADD(year , -30 , GETDATE()))
    );

    SELECT EmpName , BirthDate ,
    CASE
    WHEN DATEADD(YY , DATEDIFF(yy , BirthDate , GETDATE()) , BirthDate) < GETDATE()
    THEN DATEDIFF(yy , BirthDate , GETDATE())
    ELSE DATEDIFF(yy , BirthDate , GETDATE()) – 1
    END AS Age
    FROM @Test;

  5. SET ANSI_NULLS ON;
    GO
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE FUNCTION dbo.GetAge
    (@DOB DATETIME,
    @ToDate DATETIME
    )
    RETURNS SMALLINT
    AS
    BEGIN
    DECLARE @Diff SMALLINT,
    @F DATETIME,
    @T DATETIME

    SELECT @Diff = DATEDIFF(month, @DOB, @ToDate) / 12,
    @F = DATEADD(year, 2000 – DATEPART(year, @DOB), @DOB),
    @T = DATEADD(year, 2000 – DATEPART(year, @ToDate), @ToDate)

    IF DATEDIFF(month, @DOB, @ToDate) % 12 = 0
    BEGIN
    IF @DOB @T
    SELECT @Diff = @Diff – 1

    IF @DOB > @ToDate AND @F < @T
    SELECT @Diff = @Diff + 1
    END

    RETURN @Diff
    END

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

  7. This solution works except for if somebody’s birthday is Feb 29th and its being measured on Feb 28th the following year. In some countries its the birthday but in most countries the legal birthday is March 1 the following (non-leap) years.

Leave a Reply

Your email address will not be published. Required fields are marked *