How to calculate age in MySQL

When programming, you hit yourself with many problems, that apparently are very simple, but require a rather difficult syntax.

On a site, i found a small problem - to calculate the age of the person and see if that person is between 2 selected ages.


For that, i used the MySQL DATE_FORMAT function and NOW function like this:

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') > DATE_FORMAT(dob, '00-%m-%d'))

Where 'dob' is the date field for date of birth.
Explanation:

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y')

This returns the difference between the birth year and the current year.
To make this function work if the user has not celebrated his birth date this year, we must decrease one. So we do:

DATE_FORMAT(NOW(), '00-%m-%d') > DATE_FORMAT(dob, '00-%m-%d')

This operation returns 1 if the user has not reached his birth date, and 0 otherwise.

Hope this will help you.