Age calcuation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Age calcuation

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-13 : 08:40:38

I am using this formula to calcualte the age of a patient.
I need to calculate it from the current date.
age = DATEDIFF(mm,(convert(datetime, (convert (varchar,(c.birthMonthNum)) + '/'
+ convert(varchar,(c.BirthDayNum)) + '/'
+ convert(varchar,(c.birthYearNum))))),getdate())/12

if the current date is 5/13/2011 and the birthdate of the person is
5/15/2002 i get 9 but it should be 8
5/19/1962 i get 49 but it should be 48

can anyone please help me.
thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 09:25:17

Here is a simple way to calculate age courtsey of Itzik Ben-Gan from his book Inside Microsoft® SQL Server® 2008: T-SQL Querying


SELECT
(
   CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
   - 
   CAST(CONVERT(CHAR(8), DateOfBirth, 112) AS INT)
)/10000

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-13 : 11:10:27

i used this and it works based upon the month of the birthday to currentdate

CASE
WHEN (c.BirthdayNum <= 0 or c.BirthMonthNum <= 0 or c.BirthYearNum <= 0)
THEN 0
WHEN (rtrim(c.BirthMonthNum)*100 + c.BirthDayNum > Month(getdate())*100 + Day(getdate()) )
THEN rtrim(convert(int,datediff(yyyy,convert(datetime, rtrim(convert(char(2),c.BirthMonthNum))+'/'+
rtrim(convert(char(2),c.BirthDayNum))+'/'+
rtrim(convert(char(4),c.BirthYearNum))),getdate())- 1 ))
ELSE
rtrim(convert(int,datediff(yyyy,convert(datetime, rtrim(convert(char(2),c.BirthMonthNum))+'/'+
rtrim(convert(char(2),c.BirthDayNum))+'/'+
rtrim(convert(char(4),c.BirthYearNum))),getdate())))
END as Age,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources