RE: Calculating Age 1

[allyne]

Hi Everyone,

I need to figure out how to calculate a persons age from their birthdate...For example if a persons birthdate was 07/06/1961 the output of my query should say 43.

Thanks for your help!!!

 

[Pattycake245]

Try this:

select datediff(mm,'07/06/1961',getdate())/12

Tim

 

[vongrunt]

I don't like to nitpick, but this isn't always accurate thanks to nature of DATEDIFF():

declare @today smalldatetime
set @today = '07/31/2005'

select datediff(mm, '07/06/1961', @today)/12

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[gmmastros]

There's a good thread that occurred a couple months ago dealing with this particular topic. Several solutions are presented.

http://www.tek-tips.com/viewthread.cfm?qid=1010795

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

IMO problem is a bit simpler - only number of elapsed years is required.

Here is one variation similar to JamesLean's reply found in thread183-1049083:

declare @today smalldatetime, @dob smalldatetime
set @today = getdate()
set @dob = '07/06/1961'

select datediff(yy, @dob, @today) -
	case when right(convert(varchar(8), @today, 112), 4) < right(convert(varchar(8), @dob, 112), 4)
	then 1
	else 0 end

This one is damn kewl... too bad leap years make it not perfect:

select year(@today - @dob) - 1900

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[r937]

There's a good thread that occurred a couple months ago dealing with this particular topic.

i think the best thing to take away from that thread is: "Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil!"


here's a slight variation on vongrunt's/jameslean's query that does not use CONVERT or string functions (which are relatively slow) --

select dob
     , datediff(yy,dob,getdate())
      - sign(
         floor(datepart(dy,dob)
              /datepart(dy,getdate()))) as age
  from daTable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Pattycake245]

Yes Vongrunt after I posted that, I started to play around with it and noticed that as well. Whether you use seconds, minutes, hours or whatever it still isn't 100% accurate. I knew there were posts out there on this subject.

Tim

 

[allyne]

Hi Everyone,

WOW!!!! Thanks for all your help!!!!