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

1472

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

• ## sql-server

• Gul Hafiz

0

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)``````

• Gul Hafiz

0

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

• B Butts

0

I have used this query in our production code for nearly 5 years:

``````SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
``````