ImproperUsername ImproperUsername - 4 months ago 11
MySQL Question

MySQL date greater than returns all rows

showdate
is a datetime field - the following sql returns all rows, not only the ones with dates later than 2016-08-31.

SELECT * FROM shows WHERE showdate > '2016-08-31'


I did a workaround by creating a unixdate field, and making the query like this:

SELECT * FROM shows WHERE udate >1472626800


However, from what I read, the former should work, and I am curious as to why it doesn't.

Answer
SELECT * FROM shows where udate > 1472626800 AND showstate = 'MO' OR showstate = 'KS'

Keep in mind rules of precedence in boolean expressions.

X AND Y OR Z

This is the same as:

(X AND Y) OR Z

Which is true anywhere Z is true, even if X and Y are false.

To fix this, add parentheses explicitly to override the default boolean operation precedence:

X AND (Y OR Z)