CODEWITHSUNDEEP
mysqlsql
user967451
user967451

Reputation:

SELECT * users who are a certain age

In the users table I have a field called dob which stores the date of birth of the user in this format:

1959-04-02

Anyway I would like to SELECT all users who are 18 and 19 years old. This is my query:

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

But it only seems to select mostly 18 year olds and some (but not all) 19 year olds. I have a bunch of test users in the db and the number of 18 and 19 year olds are equal. Yet this query yields about 90% 18 year olds and about 10% 19 year olds. Why?

Upvotes: 7

Views: 7116

Answers (3)

David Grenier
David Grenier

Reputation: 1241

I would advise something like: 

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

Technically that will include people who turn 20 today. You can change the interval to a DATE_ADD and be more precise if you'd like.

This is a bit cleaner, because you don't need to rework the two birthdays in PHP (or god forbid, manually) each time.

Upvotes: 4

Banditvibe
Banditvibe

Reputation: 347

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

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

Upvotes: 4

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

People who are between 18 and 19 years old encompass a range of birthdates over 2 years. Your query only covers 1 year. Consider reworking your logic.

Upvotes: 4

Related Questions