Calculate Age using Date Field
Posted
by BRADINO
on Bradino
See other posts from Bradino
or by BRADINO
Published on Thu, 19 Feb 2009 00:45:08 +0000
Indexed on
2010/03/23
5:22 UTC
Read the original article
Hit count: 677
Filed under:
mysql
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
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
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`
FROM `people`
Find youngest person
SELECT MIN(DATE_FORMAT( FROM_DAYS( TO_DAYS( now( ) ) - TO_DAYS( `dob` ) ) , '%Y' ) +0) AS `age`
FROM `people`
FROM `people`
© Bradino or respective owner
