User Tools

Site Tools


db:sql_calculate_age

Calculating the age based on a date of birth field

19.10.2010

This can be done calculating the difference in years, and then, taking into account the day of birth function of today: if in the future, we should -1 from age, because that person isn't there yet ;)

Using MySQL

SELECT
((DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(date_of_birth_field,'%Y')) - 
      (DATE_FORMAT(NOW(),'00-%m-%d') < DATE_FORMAT(date_of_birth_field,'00-%m-%d'))) AS age 
FROM some_table WHERE 1

Using PostgreSQL

PostgreSQL has a nice function called age.

SELECT EXTRACT(YEAR FROM AGE(NOW(), date_of_birth_field))
FROM some_table WHERE 1

db/sql_calculate_age.txt · Last modified: 2013/03/16 17:40 (external edit)