Jake Jake - 1 year ago 83
SQL Question

SQL WHERE Clause Criteria

Having a Friday moment...trying to write MySQL criteria as follows:

A timestamp between two dates
ALSO, the timestamp is not greater than 23:59 if field2 is a or b

Something like:

(`timestamp` BETWEEN @StartDate And @EndDate) AND NOT ((`timestamp`>='23:59') AND (`Field2` NOT IN ('a','b')))

This looks a bit off to me, and is showing the error character in my editor, so I'm trying to determine if this form is wrong, or if I've miscounted parentheses somewhere. I'm actually performing some calculations on the timestamp field, but have omitted those for simplicity's sake.

Any insights are appreciated!

Answer Source

Other than trying to avoid OR for indexing purposes anything wrong with something like this?

... AND (f2 NOT IN ('a', 'b') OR ts <= '23:59')

Edit1: (if I remember my boolean logic transformations correctly) it is equivalent to:

... AND NOT (f2 IN ('a', 'b') AND ts > '23:59')

Edit2: or, to look at it another way, the first version can be expanded to this:

... AND (f2 NOT IN ('a', 'b') OR (f2 IN ('a', 'b') AND ts <= '23:59'))

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download