caculate age from birthdate in SQL stored procedure

Question

caculate age from birthdate in SQL stored procedure

sqlfriend

SSC Guru

Points: 52781
More actions
December 10, 2011 at 8:20 pm

#264724

I would write something in a stored procedure.
If age<23, but we don't have age colum, but only a birthdate column, I do by below,
ROUND((DATEDIFF(mm,CAST([Birthdate] AS DATE),GETDATE()) / 12),0) <18 ,
is this correct? I did a cast part because orginally this column is a varchar
also the round part, I am not sure what does 0 mean?
Thanks,
or any other ways of rewriting the below.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

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

Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.

This function returns age in years.

Age Function F_AGE_IN_YEARS:

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

My rules of thumb for date/datetime data:

1) always store in the appropriate data type (date / datetime)

2) don't put functions on columns, so if you need e.g. 23 year old use

where mydate > dateadd(yy, -23, getdate() )

in stead of "where datediff(yy, mydate, getdate()) >= 23 " because the latter one is not considered to be sargeble and indexes on your mydate column cannot be used in an optimal way to solve this criteria.

3) stick with your column in date/datetime data type as long as you can. Meaning some people tend to convert to (var)char data type because they want to hand over formatted data to their calling app. Let the calling app handle the formatting itself !

4) PLEASE DO NOT PROVIDE A DATE/DATETIME HANDLING FUNCTION AS IT HIDES WHAT ONE REALLY WANTS TO DO FOR ANYONE TROUBLESHOOTING THE QUERY AND IT MAY EVEN PREVENT THE OPTIMIZER TO GENERATE AN OPTIMAL PLAN.

( shouting intended, but not in an offensive way! It is a warning. )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

You don't really need a function for this although you could certainly put it in a function for programming consistency.

SELECT DATEDIFF(yy,BirthdayColumn,GETDATE())

- CASE

WHEN GETDATE() < DATEADD(yy,DATEDIFF(yy,BirthdayColumn,GETDATE()),BirthdayColumn)

THEN 1

ELSE 0

END

FROM dbo.SomeTable

I agree with Johan, though. If you don't need the actual number of years to be displayed, his solution would be the better way to go.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Thanks all for the ideas.

The reason I use cast the birthdate is because the birthdate column is varchar in a staging table.

The staging table comes from a ssis package load from flat file source.

we load the raw data in and keep the default varchar50 to the staging table, and didn't do the data type conversion in ssis, for the raw data helps us later in troubleshooting.

Then we use a stored procedure convert the data type and insert the data from the staging table into real target table.

And also in the sproc we filter out the birthdate <23

sroumel SSC Veteran Points: 262 More actions · Answer 5

I think that adding a WHERE in your stored procedure will work

e.g.

UPDATE table

SET col1 = 'some value'

WHERE DATEDIFF(yy,Birthdate,getdate()) < 23

unless i miss something...