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?
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?
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.