MySQL - WHERE and BETWEEN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

MySQL - WHERE and BETWEEN

Post 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
ryos
Forum Newbie
Posts: 16
Joined: Tue Feb 14, 2006 4:55 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

Actually it works without any parentheses... I'll go hide somewhere now. :?
Post Reply