Knowers of all (I have cross-posted this question to two different lists hence the BCC to not cause unwanted traffic so reply either on list or directly to me off list, its your choice);
I am trying to execute the following MySQL statement and am coming up a croppa'
SELECT name FROM `visitor` WHERE dob = DATE_SUB(CURDATE(), INTERVAL 21 YEAR)
This is to select everyone from a table who is 21 years old, just as an example. The statement runs and returns with no errors but returns no entries (and there are entries that are 21 years old)
Ultimately I am going to do something like this (I am just free-writing this so the syntax is probably wrong but hopefully you will get the idea);
SELECT name FROM `visitor` WHERE dob BETWEEN DATE_SUB(CURDATE(), INTERVAL 21 YEAR) AND DATE_SUB(CURDATE(), INTERVAL 30 YEAR)
to get everyone who is between X and Y years old. Do people think the problem is in my statement or my database?
On 06/04/10 11:45, James Bensley wrote:
I am trying to execute the following MySQL statement and am coming up a croppa'
SELECT name FROM `visitor` WHERE dob = DATE_SUB(CURDATE(), INTERVAL 21 YEAR)
If I read this correctly, you're looking for someone who is exactly 21 years old. Ie today is their 21st birthday. For example, right now: SELECT DATE_SUB(CURDATE(), INTERVAL 21 YEAR); .. gives me "1989-04-06"
Presumably SELECT name FROM `visitor` WHERE dob <= DATE_SUB(CURDATE(), INTERVAL 21 YEAR)
.. would get what you want?
SELECT name FROM `visitor` WHERE dob BETWEEN DATE_SUB(CURDATE(), INTERVAL 21 YEAR) AND DATE_SUB(CURDATE(), INTERVAL 30 YEAR)
to get everyone who is between X and Y years old. Do people think the problem is in my statement or my database?
Have you tried this? It looks pretty close to me.
I am, of-course, making assumptions about the nature of your dob field; I assume it is a DATE?
James Bensley wrote:
I am trying to execute the following MySQL statement and am coming up a croppa' SELECT name FROM `visitor` WHERE dob = DATE_SUB(CURDATE(), INTERVAL 21 YEAR)
This is to select everyone from a table who is 21 years old, just as an example. The statement runs and returns with no errors but returns no entries (and there are entries that are 21 years old) Do people think the problem is in my statement or my database?
Possibly because that statement will only match dates that are /exactly/ 21 years ago - i.e. to the day:
mysql> select DATE_SUB(CURDATE(), INTERVAL 21 YEAR); +---------------------------------------+ | DATE_SUB(CURDATE(), INTERVAL 21 YEAR) | +---------------------------------------+ | 1989-04-06 | +---------------------------------------+
You probably just want to modify it so that the results match a range greater than [exactly] 21 years ago and less than [exactly] 22 years ago, e.g.
SELECT name FROM `visitor` WHERE dob > DATE_SUB(CURDATE(), INTERVAL 21 YEAR) and dob < DATE_SUB(CURDATE(), INTERVAL 22 YEAR)
or something like that - I'm sure there's a neater way to do it!
Cheers, Simon
This has been solved, I misinterpreted the SUB_DATE() function output, for anyone's reference the answer I needed was
SELECT name FROM visitor WHERE dob <= DATE_SUB(CURDATE(), INTERVAL 21 YEAR) AND dob >= DATE_SUB(CURDATE(), INTERVAL 25 YEAR)
Sorry for the noise ;)
-
James Bensley -
Mark Rogers -
Simon Ransome