SELECT * users over a certain age

Question

In the users table, I have a field called dob that stores the user's date of birth in this format:

 1959-04-02 

In any case, I would like to select all users who are 18 and 19 years old. This is my request:

 SELECT * FROM `users` WHERE dob BETWEEN '1993-10-30' AND '1994-10-30' 

But it only seems that they choose mainly 18 year olds and some (but not all) 19 year olds. I have a bunch of test users in db, and the number of 18 and 19 year olds is the same. However, this query gives about 90% of 18 year olds and about 10% of 19 year olds. Why?

Answer 1

People who are between the ages of 18 and 19 cover a birth range of more than 2 years. Your request covers only 1 year. Consider refining your logic.

Answer 2

I would advise something like:

 SELECT * FROM `users` WHERE dob BETWEEN CURDATE() - INTERVAL 18 YEARS AND CURDATE() - INTERVAL 20 YEARS 

Technically, this will include people who are 20 years old today. You can change interval to DATE_ADD and be more precise if you want.

This is a little cleaner because you don’t need to recycle two birthdays every time in PHP (or god forbid, manually).

Answer 3

I don't have enough reputation to comment, but David Grenier's answer will work if you put a large interval first (and YEAR not YEARS, as indicated)

 SELECT * FROM `users` WHERE dob BETWEEN CURDATE() - INTERVAL 20 YEAR AND CURDATE() - INTERVAL 18 YEAR