Page 1 of 1
MySQL - WHERE and BETWEEN
Posted: Thu Feb 23, 2006 4:51 pm
by Ree
I have noticed the following SQL doesn't work as I expected, specifically the BETWEEN part (the resulting records aren't between the two values). How do I make the BETWEEN clause work in this case?
Code: Select all
SELECT id, name, date FROM events WHERE date BETWEEN integer_date1 AND integer_date2 AND location='my_location' ORDER BY date DESC, name ASC
Posted: Thu Feb 23, 2006 7:20 pm
by ryos
This may seem silly, but are `date`, `integer_date1` and `integer_date2` all in the same format, and in a format that is appropriate for a BETWEEN comparison?
Posted: Thu Feb 23, 2006 9:11 pm
by feyd
I'd think that precedence may be biting your butt on this one. Specfically, MySQL may not be abel to tell if you want date to be between integer_date1 and integer_date2 or the boolean result of integer_date2 and location.
Add some parentheses in there for good measure. ... and you might need to add some backticks around 'date' at least, maybe not now, but in the future you likely will.
Posted: Fri Feb 24, 2006 2:45 am
by Ree
Well, I tried this and doesn't seem to help...
Code: Select all
SELECT id, name, `date` FROM events WHERE `date` BETWEEN (integer_date1 AND integer_date2) AND location='my_location' ORDER BY `date` DESC, name ASC
Posted: Fri Feb 24, 2006 3:09 am
by feyd
Code: Select all
SELECT id, name, `date` FROM events WHERE (`date` BETWEEN integer_date1 AND integer_date2) AND location='my_location' ORDER BY `date` DESC, name ASC
Posted: Fri Feb 24, 2006 3:33 am
by Ree
Actually it works without any parentheses... I'll go hide somewhere now.
