• support@answerspoint.com

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

2494

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

3Answer


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)

 

  • answered 8 years ago
  • 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

 

  • answered 8 years ago
  • 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

 

  • answered 8 years ago
  • G John

Your Answer

    Facebook Share        
       
  • asked 8 years ago
  • viewed 2494 times
  • active 8 years ago

Best Rated Questions