If you are running an online community, you may have to show the age of a user, depending on the date of birth he provided on its profile. There are several solutions for it, but sometimes, it may be useful to get this information directly from the MySQL query.

SET @dateofbirth = ’1987-05-30′;
SELECT (SELECT EXTRACT(YEAR FROM CURRENT_DATE) – EXTRACT(YEAR FROM @dateofbirth) – (CASE WHEN EXTRACT(MONTH FROM CURRENT_DATE) < EXTRACT(MONTH FROM @dateofbirth) THEN 1 ELSE (CASE WHEN (EXTRACT(DAY FROM CURRENT_DATE) < EXTRACT(DAY FROM @dateofbirth) AND EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM @dateofbirth)) THEN 1 ELSE 0 END) END)) AS Age

You must give the date the way MySQL likes it : YY-MM-DD.

The result of the example above will be the age that has today a person born the 30th of May 1987 (« today » = date this article was written). We used SET in this example to simplify and to avoid querying data in a user table.

If you have, for instance, a table called Users, which owns a column that contains dates of birth of all users of the community (called Date_of_birth), you could make your MySQL query this way :

SELECT Username, Date_of_birth, (SELECT EXTRACT(YEAR FROM CURRENT_DATE) – EXTRACT(YEAR FROM Date_of_birth) – (CASE WHEN EXTRACT(MONTH FROM CURRENT_DATE) < EXTRACT(MONTH FROM Date_of_birth) THEN 1 ELSE (CASE WHEN (EXTRACT(DAY FROM CURRENT_DATE) < EXTRACT(DAY FROM Date_of_birth) AND EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM Date_of_birth)) THEN 1 ELSE 0 END) END)) AS Age FROM Users

Selon la « date d’aujourd’hui », le résultat devrait ressembler à ceci :

Ce message est également disponible en : French