Get Age from Date field in MySQL

Hello,

I was wondering if someone can tell me how to get age (in years) from Date field in the MySQL database.

For example, I need to search the database for a member who is more than 18 and less than 20 years old.

SELECT * FROM members WHERE date_converted_to_age>18 AND date_converted_to_age<20

So, what do I write instead of date_converted_to_age?

Thank you in advance for all your help! :slight_smile:

SELECT 
  col1,
  col2, 
  ...
  DATE_FORMAT(NOW(), '&#37;Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
FROM members
WHERE 
  age = 19

Credit to this post for the calculation:

not to pick on you, dan, but …

that calculation works, but it’s crude and totally disrespectful of datatypes, something that is sadly all too prevalent where mysql is concerned

for example, you’re subtracting strings, which is a syntax error in every other database system

but be that as it may…

you cannot use a column alias in the WHERE clause, you need to “push down” the alias into a subquery –

SELECT *
  FROM ( SELECT [i][COLOR="Blue"]list columns you want[/COLOR][/i]
              , [i][COLOR="blue"]expression to calculate age[/COLOR][/i] AS age
           FROM members
       ) AS d
 WHERE age = 19

by the way, my preferred age calculation is:

SELECT YEAR(CURRENT_DATE)
       - YEAR(dob)
       - CASE WHEN MONTH(CURRENT_DATE)
                 > MONTH(dob)
              THEN 0
              WHEN MONTH(CURRENT_DATE)
                 < MONTH(dob)
              THEN 1
              WHEN DAYOFMONTH(CURRENT_DATE)
                 < DAYOFMONTH(dob)
              THEN 1
              ELSE 0 END    AS age
  FROM ...

this is basically the same, only using numeric calculations

:cool:

Thank you guys for your replies.