Wednesday, December 08, 2004

Selecting an age from a birthday date field in mySQL

I added this to the mySQL manual a while back, but it seems to have been filtered out in more recent versions of the docs. Although I prefer to do my arithmetic in PHP, this query will determine an age from a date field birthDate in mySQL.

SELECT YEAR(NOW()) - YEAR(birthDate) - IF ( MONTH(NOW()) < MONTH(birthDate), 1, 0 ) - IF ( MONTH(NOW()) = MONTH(birthDate) AND DAYOFMONTH(NOW()) < DAYOFMONTH(birthDate), 1, 0) AS age

2 comments:

Anonymous said...

This is fixed:

SELECT YEAR(NOW()) - YEAR(birthday) - IF ( MONTH(NOW()) < MONTH(birthday), 1, 0 ) - IF ( MONTH(NOW()) = MONTH(birthday) AND DAYOFMONTH(NOW()) < DAYOFMONTH(birthday), 1, 0) AS age, birthday, now() from profile where id = 37;

Anonymous said...

Thanks for this code!