Calculating age - How's this one?

[astericks]

I'm just wondering if this select statement is good for age calculation:

Note : dob is date of birth in the format YYYY-MM-DD

SELECT YEAR(curdate()) - YEAR(dob) - IF(dayofyear(curdate()) < dayofyear(dob),1,0) as age from tbl_name

Only situation it chokes is when dayofyear(dob) = dayofyear(curdate()).

So, is this one ok, or is there something I overlooked? Also, if htere's a better one, can anyone post it here?

cheers
asT.

[r937]

that's as good as it gets

there's something almost like it in the mysql docs
http://www.mysql.com/doc/en/Date_calculations.html

except that they use RIGHT(CURRENT_DATE,5) which obviously depends on character conversion to yyyy-mm-dd format and they are comparing the mm-yy strings

yours is numeric, and more robust

also, when dayofyear(dob) = dayofyear(curdate()), i would not say this chokes, i would say it gives the right answer -- on your birthday, you can go ahead and say you're a year older

rudy