Calculate age from birthday with MySQL

By Nisse Pettersson at May 20, 2010 11:15
Filed Under: Code
Share on Facebook

The e-date service i'm running has no statistics on the average age of it's members. logo-mysql-110x57This is something I feel is needed and so I made my own.  This query will handle the leap year. Replace [COLUMN] with the field with birthdate and [TABLE] with the table name.

SELECT EXTRACT( YEAR
FROM (
FROM_DAYS( DATEDIFF( NOW( ) , [COLUMN]) ) ) )
 +0 AS age
FROM [TABLE]

This will give you the age of all users and if you just need the average age use the following query.

SELECT AVG( EXTRACT( YEAR
FROM (
FROM_DAYS( DATEDIFF( NOW( ) , [COLUMN]) ) ) )
) +0 AS age
FROM [TABLE]

 

Technorati-taggar: