CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
sugunan
sugunan

Reputation: 4456

MySQL query by age range on a table which have date of birth

I have MySQL table with a date of birth. And I want to query the rows which based on age for current date. So following is the query for the table with date range.

SELECT * FROM `user` WHERE `dob` > '1980-01-20' AND `dob` < '1990-01-20';

How can we convert this query to search with age range for the current date in the same table?

Upvotes: 3

Views: 6674

Answers (4)

Soniya
Soniya

Reputation: 608

SELECT *
FROM user
WHERE TIMESTAMPDIFF(YEAR, dob, CURDATE()) >= 25
AND TIMESTAMPDIFF(YEAR, dob, CURDATE()) <= 30

Upvotes: 2

Jo&#235;l Salamin
Jo&#235;l Salamin

Reputation: 3576

Here is the query you're looking for:

SELECT U.*
    ,DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),U.dob)), ‘%Y’)+0 AS age
FROM `user` U
WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),U.dob)), ‘%Y’)+0 BETWEEN 20 AND 30

In this example, you'll have every users with age in the range 20-30.

Hope this will help you

Upvotes: 4

radar
radar

Reputation: 13425

Here is an example to get all users with age between 15 and 20 years

SELECT * FROM users 
WHERE birthday <= now() - INTERVAL 15 YEAR and
      birthday > now() - INTERVAL 21 YEAR

Upvotes: 2

Mureinik
Mureinik

Reputation: 311393

You could use the year function to extract the year part of the current date. So, for example, if you're looking for users between the ages of 24 and 34:

SELECT * 
FROM   `user` 
WHERE  (YEAR(NOW()) - YEAR(`dob`)) BETWEEN 24 AND 34

Upvotes: 5

Related Questions