CODEWITHSUNDEEP
phpmysqlzend-frameworkdate
Awan
Awan

Reputation: 18590

How to get Age using BirthDate column by MySQL query?

I have a BirthDate column in MySQL database table to store user's date of birth. Now I have form in html/php with two fields(1. Age From 2. Age To).

If user want to get all users where their ages are between 10 years and 20 years, Is it possible to do this with MySQL query using BirthDate column.

Thanks

Upvotes: 5

Views: 8896

Answers (5)

Sam Code
Sam Code

Reputation: 41

It can also be achived by using a sub query: 

select *
    from (select *, TIMESTAMPDIFF(YEAR, birthday, NOW()) as age from table_name) as sub_query
    where age between 10 AND 20

Upvotes: 0

Romain Guidoux
Romain Guidoux

Reputation: 2961

Another solution which checks the year and the month:

SELECT * FROM yourTable WHERE FLOOR(DATEDIFF(curdate(), birthdate) / 365.25) BETWEEN 10 AND 20

Upvotes: 2

soulkphp
soulkphp

Reputation: 3833

Your query would look similar to this:

SELECT * FROM your_table WHERE YEAR(CURDATE())-YEAR(BirthDate) BETWEEN 10 AND 20;

Upvotes: 9

Shakti Singh
Shakti Singh

Reputation: 86406

You can get like this 

SELECT column1, column2, 
   DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(dob)), '%Y') + 0 as age     
WHERE age >10 AND age <20 ;

Upvotes: 8

Nicola Cossu
Nicola Cossu

Reputation: 56387

select * from table where
(year(curdate())-year(dob))-(right(curdate(),5)< right(dob,5) )
between 10 and 20

Upvotes: 5

Related Questions