How to get age from date of birth

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 :

Username Date_of_birth Age
Phil14 1991-12-02 14:31:19 17
MaryJuicy03 1966-02-12 14:32:34 42
Richard23 1997-12-18 14:33:29 10

 

Ce message est également disponible en : French

9 thoughts on “How to get age from date of birth

  1. Nike Air Max Terra Sert

    Amat????rioppaat Arto ja Jouko p?¤?¤ttiv?¤t tutustua Stalinin Datshaan, joka sijaitsee Sotshin ja kisakaupunki Adlerin v?¤liss?¤. Maailman ep?¤virallisella diktaattorilistalle korkealle kohotetun julmurihallitsijan asumus her?¤tt?¤?¤ oppaissa ristiriitaisia tunteita. Ehk?¤ h?¤nen historiansa tunteminen on silti hy??dyllist?¤.

  2. Nike Lunar Elite+2 Womens

    The elitism is certainly there. However, we should consider that the majority of literary pursuits are conducted in contexts even more elitist than a literature festival (and I include classrooms here) which has the merits of being free and open to everyone. Consistency would demand that every context in which literature is discussed among a homogenous group that has more or less the same interests be one in which it cannot go beyond that context. This would hold equally for events run by progressive or working-class groups. But it is more usual to find literary and political discussion transcending its original context or being sought out by others who are excited by its novelty, by its coming from someone different from ourselves. Progressive thought particularly defies claims of ownership.

Leave a Reply

Your email address will not be published. Required fields are marked *