Help calculating the age from the date of birth in table

Question

Post reply

Help calculating the age from the date of birth in table

tinamiller1

SSC-Addicted

Points: 436
More actions
March 2, 2015 at 3:55 pm

#148639

I have a 5.5 million row table that has the dateofbirth as a short date. The format looks like 1946-09-20.
I am trying to calculate the age using a SQL query but am having issues in SQL Server with errors. I have tried datediff and dateadd but nothing works.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

What is the column specification for the date?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Do you have errors in your calculations (getting incorrect age) or errors thrown by SQL Server that might come from incorrect data?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You can use the function on the link below to calculate Age using @START_DATE = birthdate and @END_DATE = today.

Age Function F_AGE_IN_YEARS

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

It's been a while since I'd use or suggest a scalar function as a solution to a problem.

Instead, this should do it.

SELECT dob,

CASE WHEN DATEADD(YYYY, DATEDIFF(YYYY, dob, GETDATE()), dob) > GETDATE()

THEN DATEDIFF(YYYY, dob, GETDATE()) - 1

ELSE DATEDIFF(YYYY, dob, GETDATE())

END

FROM (VALUES(CAST('19851115' as date)), ('19850131'), ('19850302'), ('19840302'))x(dob) --Sample dates

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2