CODEWITHSUNDEEP
mysql
Jithin Varghese
Jithin Varghese

Reputation: 2228

How to get user details between two ages from dob without age column mysql

I want to get details of the user between two ages from date of birth. My conditions are,

  1. Calculate the age from DOB
  2. Find the user details between two ages

My tables is,

id    username    phone    dob
-------------------------------------
1     Jithin     123456    31/12/1990
2     Binoy      235612    12/12/1991
3     Jibin      353453    12/12/1996

I have create mysql query as below

SELECT username, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age FROM tbl_user WHERE age BETWEEN 20 AND 30

But the above code shows an error the column age not found. How to fix this problem. Any alternate way to find age from dob and compare it.

Upvotes: 0

Views: 51

Answers (2)

juergen d
juergen d

Reputation: 204756

It does not work because you can't use an alias right away. Use a subquery around it or repeat the calculation. There is a shorter way to calculate it:

SELECT TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
from tbl_user 
where TIMESTAMPDIFF(YEAR, dob, CURDATE()) between 20 and 30

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use subquery:

SELECT *
FROM (SELECT  username,
        DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - 
        (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age 
    FROM tbl_user) sub
WHERE sub.age BETWEEN 20 AND 30;

Column age from SELECT clause is not visible in WHERE at the same level. You could either wrap it with subquery or repeat entire expression.

Upvotes: 1

Related Questions