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

Question

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

User · Answer

We used something like here  but then taking the average age   ROUND avg CONVERT int DATEDIFF hour DOB GETDATE    8766 0   0  AS AverageAge   Notice  the ROUND is outside rather than inside  This will allow for the AVG to be more accurate and we ROUND only once  Making it faster too

User · Answer

Declare  dob datetime Declare  today datetime  Set  dob    05 20 2000  set  today   getdate    select  CASE             WHEN dateadd year  datediff  year   dob   today    dob   gt   today              THEN datediff  year   dob   today  - 1             ELSE datediff  year   dob   today          END as Age

User · Answer

I have used this query in our production code for nearly 10 years   SELECT FLOOR  CAST  GetDate   AS INTEGER  - CAST Date of birth AS INTEGER     365 25  AS Age

User · Answer

I believe this is similar to other ones posted here     but this solution worked for the leap year examples 02 29 1976 to 03 01 2011 and also worked for the case for the first year   like 07 04 2011 to 07 03 2012 which the last one posted about leap year solution did not work for that first year use case   SELECT FLOOR DATEDIFF DAY   date1    date2    365 25    Found here

User · Answer

After trying MANY methods  this works 100  of the time using the modern MS SQL FORMAT function instead of convert to style 112  Either would work but this is the least code   Can anyone find a date combination which does not work  I don t think there is one     --Set parameters  or choose from table column instead   DECLARE  DOB    DATE    2000 02 29  -- If  DOB is a leap day             ToDate DATE    2018 03 01  --   there birthday in this calculation will be   --0  part tells SQL to calc the char 8  as numbers  SELECT  Age     0  FORMAT  ToDate  yyyyMMdd   - FORMAT  DOB  yyyyMMdd      10000

User · Answer

select DATEDIFF yy  DATE GETDATE    - case when DATEPART mm GETDATE    100 DATEPART dd GETDATE    gt   DATEPART mm  DATE  100 DATEPART dd  DATE  THEN 0 ELSE 1 END

User · Answer

you should count years by following way  -  select cast datediff DAY   2000-03-01 10 00 01    2013-03-01 10 00 00      365 23076923074  as int  as  Age    it s very easy

User · Answer

Here is how i calculate age given a birth date and current date   select case              when cast getdate   as date    cast dateadd year   datediff year   1996-09-09   getdate       1996-09-09   as date                  then dateDiff yyyy  1996-09-09  dateadd year  0  getdate                 else dateDiff yyyy  1996-09-09  dateadd year  -1  getdate             end as MemberAge go

User · Answer

DECLARE  DOB datetime set  DOB   11 25 1985   select floor    cast convert varchar 8  getdate   112  as int - cast convert varchar 8   DOB 112  as int      10000     source  http   beginsql wordpress com 2012 04 26 how-to-calculate-age-in-sql-server

User · Answer

select floor  datediff day 0  today  - datediff day 0  birthdate     365 2425  as age   There are a lot of 365 25 answers here  Remember how leap years are defined    Every four years   except every 100 years   except every 400 years

User · Answer

Try this solution   declare  BirthDate datetime declare  ToDate datetime  set  BirthDate    1 3 1990  set  ToDate    1 2 2008  select  BirthDate  Date of Birth    ToDate  ToDate   case when  DatePart mm  ToDate   lt   Datepart mm  BirthDate            OR  DatePart m  ToDate    Datepart m  BirthDate  AND DatePart dd  ToDate   lt  Datepart dd  BirthDate           then  Datepart yy   ToDate  - Datepart yy   BirthDate  - 1          else  Datepart yy   ToDate  - Datepart yy   BirthDate  end  Age

User · Answer

You need to consider the way the datediff command rounds   SELECT CASE WHEN dateadd year  datediff  year  DOB  getdate     DOB   gt  getdate               THEN datediff year  DOB  getdate    - 1             ELSE datediff year  DOB  getdate           END as Age FROM  lt table gt    Which I adapted from here   Note that it will consider 28th February as the birthday of a leapling for non-leap years e g  a person born on 29 Feb 2020 will be considered 1 year old on 28 Feb 2021 instead of 01 Mar 2021

User · Answer

select datediff day  1991-03-16  getdate      for days get date refers today date select datediff year  1991-03-16  getdate      for years select datediff month  1991-03-16  getdate      for month

User · Answer

CREATE function dbo AgeAtDate       DOB    datetime       CompareDate datetime    returns INT as begin  return CASE WHEN  DOB is null THEN      null ELSE  DateDiff yy  DOB   CompareDate   - CASE WHEN datepart mm  CompareDate   gt  datepart mm  DOB  OR  datepart mm  CompareDate    datepart mm  DOB  AND datepart dd  CompareDate   gt   datepart dd  DOB     THEN 0    ELSE 1   END END End  GO

User · Answer

Here is a technique which seems to work around the edge cases  the end of the year and leap Februaries  The problem with datediff year          is that it only finds the difference between the years  and not the actual dates  which is a bit na  ve in my opinion  This is only reliable if the date of birth is 1st Jan  The solution is to time warp the date of birth to the first of January  and the asking date by the same amount  Because of the leap year problem using the day of year is also unreliable  so I use the month and day to do the time warp  CREATE FUNCTION age  then AS date  now AS date  RETURNS int AS BEGIN     DECLARE  month INT   month  then -1      DECLARE  day INT   day  then -1       SET  then dateadd month - month  then       SET  then dateadd day - day  then        SET  now dateadd month - month  now       SET  now dateadd day - day  now        RETURN datediff year  then  now   END   You can write this more compactly  of course  You can even write it in one line  if that   s your idea of having a good time  CREATE FUNCTION age  then AS date  now AS date  RETURNS int AS BEGIN     RETURN  datediff          year          dateadd day -day  then  1 dateadd month -month  then  1  then            dateadd day -day  then  1 dateadd month -month  then  1  now          END   but that   s only showing off  and it   s less efficient as it needs to recalculate the offsets

User · Answer

CASE WHEN datepart MM  getdate     lt  datepart MM  BIRTHDATE  THEN   datepart YYYY  getdate    - datepart YYYY  BIRTH DATE   -1        ELSE          CASE WHEN datepart MM  getdate      datepart MM  BIRTHDATE              THEN                  CASE WHEN datepart DD  getdate     lt  datepart DD  BIRTHDATE  THEN   datepart YYYY  getdate    - datepart YYYY  BIRTHDATE   -1                       ELSE  datepart YYYY  getdate    - datepart YYYY  BIRTHDATE                   END         ELSE  datepart YYYY  getdate    - datepart YYYY  BIRTHDATE   END                 END

User · Answer

The answer marked as correct is nearer to accuracy but  it fails in following scenario - where Year of birth is Leap year and day are after February month   declare  ReportStartDate datetime   CONVERT datetime   1 1 2014     DateofBirth datetime   CONVERT datetime   2 29 1948    FLOOR DATEDIFF HOUR  DateofBirth  ReportStartDate   8766     OR   FLOOR DATEDIFF HOUR  DateofBirth  ReportStartDate   8765 82  -- Divisor is more accurate than 8766   -- Following solution is giving me more accurate results   FLOOR DATEDIFF YEAR  DateofBirth  ReportStartDate  -  CASE WHEN DATEADD YY DATEDIFF YEAR  DateofBirth  ReportStartDate   DateofBirth   gt   ReportStartDate THEN 1 ELSE 0 END      It worked in almost all scenarios  considering leap year  date as 29 feb  etc   Please correct me if this formula have any loophole

User · Answer

DECLARE  FromDate DATETIME    1992-01-2623 59 59 000             ToDate   DATETIME    2016-08-10 00 00 00 000            Years INT   Months INT   Days INT   tmpFromDate DATETIME SET  Years   DATEDIFF YEAR   FromDate   ToDate   -  CASE WHEN DATEADD YEAR  DATEDIFF YEAR   FromDate   ToDate              FromDate   gt   ToDate THEN 1 ELSE 0 END     SET  tmpFromDate   DATEADD YEAR   Years    FromDate  SET  Months    DATEDIFF MONTH   tmpFromDate   ToDate   -  CASE WHEN DATEADD MONTH DATEDIFF MONTH   tmpFromDate   ToDate              tmpFromDate   gt   ToDate THEN 1 ELSE 0 END    SET  tmpFromDate   DATEADD MONTH   Months    tmpFromDate  SET  Days    DATEDIFF DAY   tmpFromDate   ToDate   -  CASE WHEN DATEADD DAY  DATEDIFF DAY   tmpFromDate   ToDate              tmpFromDate   gt   ToDate THEN 1 ELSE 0 END    SELECT  FromDate FromDate   ToDate ToDate           Years Years    Months Months   Days Days

User · Answer

SELECT CAST DATEDIFF dy   DOB  GETDATE   1  365 25 AS int

User · Answer

Just check whether the below answer is feasible     DECLARE  BirthDate DATE    09 06 1979   SELECT      YEAR GETDATE    - YEAR  BirthDate  -   CASE  WHEN  MONTH GETDATE      100    DATEPART dd  GETDATE     gt         MONTH  BirthDate    100    DATEPART dd   BirthDate   THEN 1               ELSE 0               END

User · Answer

you should use   select  FLOOR DATEDIFF CURDATE   DATE DOB   365 25  from table name   here CURDATE   uses current date you can give own date in   yyyy-mm-dd  format DATE DOB  extract yyyy-mm-dd year from your column which is in DATETIME format  here DOB is your column name  but you should alter table to modify the data type to be DATETIME in your case which is nvarchar  Note- this query is used in mysql this return age in whole year

User · Answer

Try This   DECLARE  date datetime   tmpdate datetime   years int   months int   days int SELECT  date    08 16 84   SELECT  tmpdate    date  SELECT  years   DATEDIFF yy   tmpdate  GETDATE    - CASE WHEN  MONTH  date   gt  MONTH GETDATE     OR  MONTH  date    MONTH GETDATE    AND DAY  date   gt  DAY GETDATE     THEN 1 ELSE 0 END SELECT  tmpdate   DATEADD yy   years   tmpdate  SELECT  months   DATEDIFF m   tmpdate  GETDATE    - CASE WHEN DAY  date   gt  DAY GETDATE    THEN 1 ELSE 0 END SELECT  tmpdate   DATEADD m   months   tmpdate  SELECT  days   DATEDIFF d   tmpdate  GETDATE     SELECT Convert Varchar Max   years    Years    Convert Varchar max   months      Months   Convert Varchar Max    days   days

User · Answer

This will correctly handle the issues with the birthday and rounding   DECLARE  dob  datetime SET  dob  1992-01-09 00 00 00   SELECT DATEDIFF YEAR   0 0   getdate  - dob

User · Answer

How about this   SET  Age   CAST DATEDIFF Year   DOB   Stamp  as int  IF  CAST DATEDIFF DAY  DATEADD Year   Age   DOB    Stamp  as int   lt  0       SET  Age    Age - 1

User · Answer

So many of the above solutions are wrong DateDiff yy  Dob   PassedDate  will not consider the month and day of both dates  Also taking the dart parts and comparing only works if they re properly ordered   THE FOLLOWING CODE WORKS AND IS VERY SIMPLE   create function  dbo   AgeAtDate        DOB    datetime       PassedDate datetime    returns int with SCHEMABINDING as begin  declare  iMonthDayDob int declare  iMonthDayPassedDate int   select  iMonthDayDob   CAST datepart  mm  DOB    100   datepart   dd  DOB  AS int   select  iMonthDayPassedDate   CAST datepart  mm  PassedDate    100   datepart   dd  PassedDate  AS int    return DateDiff yy  DOB   PassedDate   - CASE WHEN  iMonthDayDob  lt    iMonthDayPassedDate   THEN 0    ELSE 1   END  End

User · Answer

Gotta throw this one out there  If you convert the date using the 112 style  yyyymmdd  to a number you can use  a calculation like this      yyyyMMdd - yyyyMMdd    10000   difference in full years  declare  as of datetime   bday datetime  select  as of    2009 10 15    bday    1980 4 20   select      Convert Char 8   as of 112       Convert Char 8   bday 112       0   Convert Char 8   as of 112  - Convert Char 8   bday 112         0   Convert Char 8   as of 112  - Convert Char 8   bday 112     10000   output  20091015    19800420    290595  29

User · Answer

SELECT ID  Name  DATEDIFF yy CONVERT DATETIME  DOB  GETDATE    AS AGE  DOB FROM MyTable

User · Answer

What about a solution with only date functions  not math  not worries about leap year  CREATE FUNCTION dbo getAge  dt datetime   RETURNS int AS BEGIN     RETURN          DATEDIFF yy   dt  getdate            - CASE              WHEN                  MONTH  dt   gt  MONTH GETDATE    OR                   MONTH  dt    MONTH GETDATE    AND DAY  dt   gt  DAY GETDATE                  THEN 1              ELSE 0          END END

User · Answer

EDIT  THIS ANSWER IS INCORRECT  I leave it in here as a warning to anyone tempted to use dayofyear  with a further edit at the end     If  like me  you do not want to divide by fractional days or risk rounding leap year errors  I applaud  Bacon Bits comment in a post above https   stackoverflow com a 1572257 489865 where he says      If we re talking about human ages  you should calculate it the way   humans calculate age  It has nothing to do with how fast the earth   moves and everything to do with the calendar  Every time the same   month and day elapses as the date of birth  you increment age by 1    This means the following is the most accurate because it mirrors what   humans mean when they say  age     He then offers   DATEDIFF yy   date  GETDATE    - CASE WHEN  MONTH  date   gt  MONTH GETDATE     OR  MONTH  date    MONTH GETDATE    AND DAY  date   gt  DAY GETDATE     THEN 1 ELSE 0 END   There are several suggestions here involving comparing the month  amp  day  and some get it wrong  failing to allow for the OR as correctly here    But nobody has offered dayofyear  which seems so simple and much shorter   I offer   DATEDIFF year   date  GETDATE    - CASE WHEN DATEPART dayofyear   date   gt  DATEPART dayofyear  GETDATE    THEN 1 ELSE 0 END    Note  Nowhere in SQL BOL MSDN is what DATEPART dayofyear       returns actually documented  I understand it to be a number in the range 1--366  most importantly  it does not change by locale as per DATEPART weekday        amp  SET DATEFIRST      EDIT  Why dayofyear goes wrong  As user  AeroX has commented  if the birth start date is after February in a non leap year  the age is incremented one day early when the current end date is a leap year  e g   2015-05-26    2016-05-25  gives an age of 1 when it should still be 0  Comparing the dayofyear in different years is clearly dangerous  So using MONTH   and DAY   is necessary after all

User · Answer

I ve done a lot of thinking and searching about this and I have 3 solutions that    calculate age correctly  are short  mostly  are  mostly  very understandable    Here are testing values   DECLARE  NOW DATETIME    2013-07-04 23 59 59   DECLARE  DOB DATETIME    1986-07-05       Solution 1  I found this approach in one js library  It s my favourite    DATEDIFF YY   DOB   NOW  -    CASE WHEN DATEADD YY  DATEDIFF YY   DOB   NOW    DOB   gt   NOW THEN 1 ELSE 0 END   It s actually adding difference in years to DOB and if it is bigger than current date then subtracts one year  Simple right  The only thing is that difference in years is duplicated here    But if you don t need to use it inline you can write it like this   DECLARE  AGE INT   DATEDIFF YY   DOB   NOW  IF DATEADD YY   AGE   DOB   gt   NOW SET  AGE    AGE - 1     Solution 2  This one I originally copied from  bacon-bits  It s the easiest to understand but a bit long   DATEDIFF YY   DOB   NOW  -    CASE WHEN MONTH  DOB   gt  MONTH  NOW       OR MONTH  DOB    MONTH  NOW  AND DAY  DOB   gt  DAY  NOW     THEN 1 ELSE 0 END   It s basically calculating age as we humans do      Solution 3  My friend refactored it into this   DATEDIFF YY   DOB   NOW  -    CEILING 0 5   SIGN  MONTH  DOB  - MONTH  NOW     50   DAY  DOB  - DAY  NOW      This one is the shortest but it s most difficult to understand  50 is just a weight so the day difference is only important when months are the same  SIGN function is for transforming whatever value it gets to -1  0 or 1  CEILING 0 5   is the same as Math max 0  value  but there is no such thing in SQL

User · Answer

Ed Harper s solution is the simplest I have found which never returns the wrong answer when the month and day of the two dates are 1 or less days apart   I made a slight modification to handle negative ages   DECLARE  D1 AS DATETIME   D2 AS DATETIME SET  D2    2012-03-01 10 00 02  SET  D1    2013-03-01 10 00 01  SELECT    DATEDIFF YEAR   D1  D2          CASE       WHEN  D1 lt  D2 AND DATEADD YEAR  DATEDIFF YEAR  D1   D2    D1   gt   D2       THEN - 1       WHEN  D1 gt  D2 AND DATEADD YEAR  DATEDIFF YEAR  D1   D2    D1   lt   D2       THEN 1       ELSE 0    END AS AGE

User · Answer

Since there isn t one simple answer that always gives the correct age  here s what I came up with    SELECT DATEDIFF YY  DateOfBirth  GETDATE    -       CASE WHEN RIGHT CONVERT VARCHAR 6   GETDATE    12   4   gt                   RIGHT CONVERT VARCHAR 6   DateOfBirth  12   4        THEN 0 ELSE 1 END AS AGE    This gets the year difference between the birth date and the current date  Then it subtracts a year if the birthdate hasn t passed yet   Accurate all the time - regardless of leap years or how close to the birthdate    Best of all - no function

User · Answer

DECLARE  yourBirthDate DATETIME    1987-05-25  SELECT YEAR DATEADD DAY  DATEDIFF DAY   yourBirthDate  GETDATE     CAST  0001-01-01  AS DATETIME2    - 1

User · Answer

What about   DECLARE  DOB datetime SET  DOB  19851125     SELECT Datepart yy convert date GETDATE   - DOB -1900   Wouldn t that avoid all those rounding  truncating and ofsetting issues

User · Answer

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

[sql-server] How to calculate age (in years) based on Date of Birth and getDate()

Examples related to sql-server

Examples related to sql-server-2005

Examples related to select

Examples related to date