SQL: Syntax error

Question

I am executing this query in MySql:

SELECT amount
FROM Prices
WHERE (item_id = 1246 AND
       ('2016-12-26' BETWEEN (effective_date AND COALESCE(end_date, NOW()))))

But for some reason I get a syntax error that I don't see where it is. the error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND(item_id = 1419 AND ('2017-01-14' BETWEEN (effective_date AND COALESCE(end' at line 1

the Price is like this: Prices

id
item_id
effective_date
end_date

Answer 1

I don't think there should be parentheses between BETWEEN and the first term of that expression. Something like this should work:

SELECT amount
FROM Prices
WHERE item_id = 1246 AND
      '2016-12-26' BETWEEN effective_date AND COALESCE(end_date, NOW())

This question is a typo, but maybe this answer would be useful to anyone who wants to know the proper way to use BETWEEN.

The MySQL documentation for BETWEEN doesn't explicitly mention anything about parentheses, but it seems to be implying this based on the examples given.

Based on testing this locally, parentheses around each of the two terms in the BETWEEN expression are OK, e.g.

WHERE '2016-12-26' BETWEEN (effective_date) AND (COALESCE(end_date, NOW()))

However, putting parenthesis around the entire clause generates an error, which is what you were doing:

WHERE '2016-12-26' BETWEEN (effective_date AND COALESCE(end_date, NOW()))