Calculate Age using Date Field
- by BRADINO
So if you have a database table that has DOB borthdays as date fields, this is an easy way to query that table based on age parameters. The following examples assume that the date of birth date field is dob and the table name is people.
Find people who are 30 years old
SELECT DATE_FORMAT( FROM_DAYS( TO_DAYS( now( ) ) - TO_DAYS( `dob` ) ) , '%Y' ) +0 AS `age`
FROM `people` HAVING `age` = 30
Find people who are 31-42 years old
SELECT DATE_FORMAT( FROM_DAYS( TO_DAYS( now( ) ) - TO_DAYS( `dob` ) ) , '%Y' ) +0 AS `age`
FROM `people` HAVING `age`>= 31 AND `age` <= 42
Find oldest person
SELECT MAX(DATE_FORMAT( FROM_DAYS( TO_DAYS( now( ) ) - TO_DAYS( `dob` ) ) , '%Y' ) +0) AS `age`
FROM `people`
Find youngest person
SELECT MIN(DATE_FORMAT( FROM_DAYS( TO_DAYS( now( ) ) - TO_DAYS( `dob` ) ) , '%Y' ) +0) AS `age`
FROM `people`
