Fetch people between a certain age from your MySQL table

Question

Well, let's say I've got the following table:

+----+-----------+--------+------------------+
| id | name      | gender | birthDate (date) |
+----+-----------+--------+------------------+
| 1  | John Doe  | Male   | 1968-09-18       |
| 2  | Jane Doe  | Female | 1970-11-20       |
| 3  | Sally Doe | Female | 1985-04-17       |
| 4  | Jake Doe  | Male   | 1989-07-10       |
| 5  | Mike Doe  | Male   | 1991-06-28       |
| 6  | Cindy Doe | Female | 1995-02-03       |
+----+-----------+--------+------------------+

How would I get everyone older than 21 and younger than 50 from this table. I can normally find my way in mysql but when it comes to dates I always seem to mess up.

Important question: is the birthDate column type a DATE or string? because that's not how mysql stores dates. — STT LCU, Jan 27 '15 at 07:54
possible duplicate of [Calculate Age in MySQL (InnoDb)](http://stackoverflow.com/questions/5773405/calculate-age-in-mysql-innodb) — Markus Müller, Jan 27 '15 at 07:55
@STTLCU It will be a date field, I will edit the question accordingly — , Jan 27 '15 at 07:58

score 0 · Answer 1 · answered Jan 27 '15 at 07:52

0

select * from your_table
where curdate() - interval 21 year >= birthDate 
and curdate() - interval 50 year < birthDate

answered Jan 27 '15 at 07:52

juergen d

201,996
37
293
362

score 0 · Accepted Answer · answered Jan 27 '15 at 08:02

0

Another way using BETWEEN statement.

SELECT *
FROM tablename
WHERE birthdate BETWEEN
(curdate() - interval 50 year)
AND 
(curdate() - interval 21 year)

answered Jan 27 '15 at 08:02

manuelbcd

3,106
1
26
39

Curious, is one of the two answers better (i.e. in performance) – Jan 27 '15 at 08:13

Fetch people between a certain age from your MySQL table

2 Answers2