RE: Calculating Age 1

allyne · Apr 27, 2005

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 · Apr 27, 2005

Try this:

Code:

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

Tim

vongrunt · Apr 28, 2005

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

Code:

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 · Apr 28, 2005

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 · Apr 28, 2005

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:

Code:

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:

Code:

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 · Apr 28, 2005

gmmastros said:
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) --

Code:

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 · Apr 28, 2005

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 · Apr 28, 2005

Hi Everyone,

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

RE: Calculating Age 1

allyne

MIS

Pattycake245

Programmer

vongrunt

Programmer

gmmastros

Programmer

vongrunt

Programmer

r937

Technical User

Pattycake245

Programmer

allyne

MIS

Similar threads

Part and Inventory Search

Sponsor