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

Question

Hi

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

Many thanks!

Answer 1

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

Answer 2

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

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

This is a knotty little problem because you have to take into account whether the burthday has occured this year to the day. I would prefer using datepart(dy,..) to get the day count but it fails on a leap year scenario and misreports the age by 1 day.

declare @DOB datetime
set @DOB = convert(datetime,'2007/10/15')

Select DateDiff(yy,@DOB,getdate()) 
- CASE WHEN datepart  (mm,@DOB) <= DatePart(mm,getdate()) and datepart(dd,@DOB) <= datepart(dd,getdate())
  THEN 0 
  ELSE 1
  END AS Age

Used the datediff, but then modify it back down a year if the birthday has not yet passed, or leave it as it is if you've already had your cards and presents :)

I 'think' that holds, but it's an annoying little problem.

Answer 5

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)

Answer 6

Gotta throw this one out there.

(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