How to find list of people whose age is between a range?

Question

I have this situation where I have date-of-birth stored in database eg. 1990/03/15. I want to find out info. of records whose age is between 20 & 30. How should I write this query?

I have tried this: I get age of all candidates using this query:

SELECT TIMESTAMPDIFF(YEAR,dob,CURDATE()) as age FROM `profile`

How to use it to get list of all candidates between age 20 & 30?

Aman Jain · Accepted Answer · 2016-01-07T14:36:36.993

2

The NOW() gives the current Date. Now, to get date 20 and 30 years older, we can do the following:

DATE_SUB(NOW(), INTERVAL 20 YEAR)
AND 
DATE_SUB(date, INTERVAL 30 YEAR)

Now the remaining query is very simple. That is,

SELECT * FROM Person 
WHERE dateOfBirth BETWEEN DATE_SUB(NOW(), INTERVAL 20 YEAR) 
                  AND DATE_SUB(date, INTERVAL 30 YEAR)

edited Jan 07 '16 at 14:36

answered Jan 07 '16 at 14:28

Aman Jain

655
5
17

1

This works ... SELECT * FROM profile WHERE dob BETWEEN DATE_ADD(NOW(), INTERVAL -20 YEAR) AND DATE_ADD(NOW(), INTERVAL -30 YEAR) – Nevermore Jan 07 '16 at 14:32
FYI.. date 20 gives the date which is 20 years ahead of today. – Nevermore Jan 07 '16 at 14:33
1

I think you need to swap the positions so that the oldest (-30) date is first in the `BETWEEN` statement. – Klas Lindbäck Jan 07 '16 at 14:42
According to this question it does: http://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql – Klas Lindbäck Jan 08 '16 at 08:08

How to find list of people whose age is between a range?

1 Answers1