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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

You need to consider the way the datediff command rounds.

SELECT CASE WHEN dateadd(year, datediff (year, DOB, getdate()), DOB) > getdate()
            THEN datediff(year, DOB, getdate()) - 1
            ELSE datediff(year, DOB, getdate())
       END as Age
FROM <table>

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.

Answer 5

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 <= @iMonthDayPassedDate
  THEN 0 
  ELSE 1
  END

End

Answer 6

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) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE()))
THEN 1 ELSE 0 END

There are several suggestions here involving comparing the month & 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) > 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, ...) & 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.

Answer 7

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.

Answer 8

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

Answer 9

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) > @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) > @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) > MONTH(@NOW) 
    OR MONTH(@DOB) = MONTH(@NOW) AND DAY(@DOB) > 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.

Answer 10

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

Answer 11

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?

Answer 12

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()) >     
 (MONTH(@BirthDate) * 100) + DATEPART(dd, @BirthDate)
 THEN 1             
 ELSE 0             
 END        
 )

Answer 13

There are many answers to this question, but I think this one is close to the truth.

The datediff(year,…,…) function, as we all know, only counts the boundaries crossed by the date part, in this case the year. As a result it ignores the rest of the year.

This will only give the age in completed years if the year were to start on the birthday. It probably doesn’t, but we can fake it by adjusting the asking date back by the same amount.

In pseudopseudo code, it’s something like this:

adjusted_today = today - month(dob) + 1 - day(dob) + 1
age = year(adjusted_today - dob)

• The + 1 is to allow for the fact that the month and day numbers start from 1 and not 0.
• The reason we subtract the month and the day separately rather than the day of the year is because February has the annoying tendency to change its length.

The calculation in SQL is:

datediff(year,dob,dateadd(month,-month(dob)+1,dateadd(day,-day(dob)+1,today)))

where dob and today are presumed to be the date of birth and the asking date.

You can test this as follows:

WITH dates AS (
    SELECT
        cast('2022-03-01' as date) AS today,
        cast('1943-02-25' as date) AS dob
)
select
    datediff(year,dob,dateadd(month,-month(dob)+1,dateadd(day,-day(dob)+1,today))) AS age
from dates;

which gives you George Harrison’s age in completed years.

This is much cleaner than fiddling about with quarter days which will generally give you misleading values on the edges.

If you have the luxury of creating a scalar function, you can use something like this:

DROP FUNCTION IF EXISTS age;
GO
CREATE FUNCTION age(@dob date, @today date) RETURNS INT AS
BEGIN
    SET @today = dateadd(month,-month(@dob)+1,@today);
    SET @today = dateadd(day,-day(@dob)+1,@today);
    RETURN datediff(year,@dob,@today);
END;
GO

Remember, you need to call dbo.age() because, well, Microsoft.

Answer 14

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/

Answer 15

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) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > 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'

Answer 16

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

Answer 17

CASE WHEN datepart(MM, getdate()) < 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()) < 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

Answer 18

How about this:

SET @Age = CAST(DATEDIFF(Year, @DOB, @Stamp) as int)
IF (CAST(DATEDIFF(DAY, DATEADD(Year, @Age, @DOB), @Stamp) as int) < 0) 
    SET @Age = @Age - 1

Answer 19

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

Answer 20

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) <  Datepart(mm,@BirthDate)) 
        OR (DatePart(m,@ToDate) = Datepart(m,@BirthDate) AND DatePart(dd,@ToDate) < Datepart(dd,@BirthDate))
        then (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate) - 1)
        else (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate))end) Age

Answer 21

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)

Answer 22

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<@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) > @D2
      THEN - 1
      WHEN @D1>@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) < @D2
      THEN 1
      ELSE 0
   END AS AGE

Answer 23

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

Answer 24

Declare @dob datetime
Declare @today datetime

Set @dob = '05/20/2000'
set @today = getdate()

select  CASE
            WHEN dateadd(year, datediff (year, @dob, @today), @dob) > @today 
            THEN datediff (year, @dob, @today) - 1
            ELSE datediff (year, @dob, @today)
        END as Age

Answer 25

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) > datepart(mm,@DOB) OR (datepart(mm,@CompareDate) = datepart(mm,@DOB) AND datepart(dd,@CompareDate) >= datepart(dd,@DOB))
  THEN 0 
  ELSE 1
  END
END
End

GO

Answer 26

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

Answer 27

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) > @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) > @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) > @ToDate THEN 1 ELSE 0 END) 

SELECT @FromDate FromDate, @ToDate ToDate, 
       @Years Years,  @Months Months, @Days Days

Answer 28

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) > MONTH(GETDATE()) OR 
                (MONTH(@dt) = MONTH(GETDATE()) AND DAY(@dt) > DAY(GETDATE())) 
            THEN 1 
            ELSE 0 
        END
END

Answer 29

declare @birthday as datetime
set @birthday = '2000-01-01'
declare @today as datetime
set @today = GetDate()
select 
    case when ( substring(convert(varchar, @today, 112), 5,4) >= substring(convert(varchar, @birthday, 112), 5,4)  ) then
        (datepart(year,@today) - datepart(year,@birthday))
    else 
        (datepart(year,@today) - datepart(year,@birthday)) - 1
    end

Answer 30

The following script checks the difference in years between now and the given date of birth; the second part checks whether the birthday is already past in the current year; if not, it subtracts it:

SELECT year(NOW()) - year(date_of_birth) - (CONCAT(year(NOW()), '-', month(date_of_birth), '-', day(date_of_birth)) > NOW()) AS Age
FROM tableName;

Answer 31

Easy and simple

SELECT (YEAR(CURRENT_TIMESTAMP) - YEAR(birthday)) as age FROM db_deirvlon_monyo_users

Answer 32

There are a lot of answers to this question. Some of them are very lengthy, prone to bugs, or imprecise. As I was scrolling through the solutions, I noticed an especially good one in the comment section. I could not resist sharing it.

SELECT
    DATEDIFF(year, birthday, GETDATE()) -
        CASE
            WHEN (
                MONTH(GETDATE()) > MONTH(birthday)
                ) THEN 1
            WHEN (
                MONTH(GETDATE()) = MONTH(birthday) AND
                DAY(GETDATE()) > DAY(birthday)
                ) THEN 1
            ELSE 0
        END AS age
FROM people

If the month and the day of the birth are more than the current month and day then the date difference is valid. Otherwise, subtract 1 from the date difference. IMO the solution is:

• Easy to understand: It does not use any string manipulation and magic numbers. It is only 10 lines of code and contains the most necessary functions.
• Follows human intuition: It has nothing to do with how fast the earth moves around the sun.
• Precise and performant: No rounding, converting, multiplying, or dividing is used. It is simply comparing and subtracting which is more efficient than dividing by a float.

Credit to @Bacon Bits's comment under this answer.

Answer 33

The problem with calculating ages with datetime format is that we do not have uniformity in the year. Not all the years have the same amount of days (365 or 366), neither the months.

The only certain thing is that every year has 12 months, so I think a right usage of the DATEDIFF() function is with month.

In this case:

SELECT ID, Name, DATEDIFF(MONTH,DOB,GETDATE())/12 -
CASE WHEN MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB)>DAY(GETDATE()) THEN 1 ELSE 0 END AS AGE, DOB
FROM myTable

Answer 34

declare @birthday varchar(12) = '1986-06-05'

select DATEDIFF(year, @birthday, getdate()) - Case 
         when month(@birthday) > month(getdate()) 
                 or (month(@birthday) = month(getdate()) 
                  and day(@birthday) > day(getdate()))  
         then 1 else 0 
      End As Age

Answer 35

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

Answer 36

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

Answer 37

If you find your age. select (months_between(sysdate,dob)/12) from table_name; If you find your approximate age. select round(months_between(sysdate,dob)/12) from table_name;enter code here There dob is column name. sysdate used for current date . months_between used for find total month dob to currentdate

Answer 38

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.

Answer 39

select DATEDIFF(yy,@DATE,GETDATE()) -
case when DATEPART(mm,GETDATE())*100+DATEPART(dd,GETDATE())>=
DATEPART(mm,@DATE)*100+DATEPART(dd,@DATE) THEN 0
ELSE 1 END 

Answer 40

SELECT CAST(DATEDIFF(dy, @DOB, GETDATE()+1)/365.25 AS int)

Answer 41

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

Answer 42

DECLARE @yourBirthDate DATETIME = '1987-05-25'
SELECT YEAR(DATEADD(DAY, DATEDIFF(DAY, @yourBirthDate, GETDATE()), CAST('0001-01-01' AS DATETIME2))) - 1