How to calculate age (in years) based on Date of Birth and getDate()
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
Date
sql-server
- asked 8 years ago
- Gul Hafiz
3Answer
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
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
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
Best Rated Questions
Your Answer