mysql WHERE '2010-08-24 11:59:59' BETWEEN c.start_date AND c.end_date;

Question

I'm currently trying to use this query

SELECT 
a.id AS id,
a.item AS item,
c.id AS campaign_id,
DATE(c.start_date) AS START,
DATE(c.end_date) AS END
FROM campaign c LEFT JOIN action_6 a
ON c.id = a.campaign_id WHERE action_id = 6
AND TIMESTAMP('2010-08-24 11:59:59') BETWEEN c.start_date AND c.end_date;

however i am getting an empty resultset due to the AND '2010-08-24 11:59:59' BETWEEN c.start_date AND c.end_date.

c.start_date and c.end_date (on the desired row) are 2010-08-23 00:00:00 and 2010-07-29 23:59:59 so it should be returning the row yes?

---

Edit: updated query to show explicit conversion.

Answer 1

Assuming that c.start_date and c.end_date are both date type values rather than VARCHARs then '2010-08-24 11:59:59' is a string. You need to convert it to a date

Answer 2

You are getting an empty resultset because the timestamp 2010-08-24 11:59:59 is not between 2010-07-29 23:59:59 and 2010-08-23 00:00:00 (see last paragraph if this isn't a typo and you really meant between 2010-08-23 00:00:00 and 2010-07-29 23:59:59).

Here are two queries to test this out.
This query returns 0 (False):

SELECT '2010-08-24 11:59:59' 
BETWEEN '2010-07-29 23:59:59' AND '2010-08-23 00:00:00'

This query returns 1 (True):

SELECT '2010-08-24 11:59:59' 
BETWEEN '2010-07-29 23:59:59' AND '2010-08-25 00:00:00'

Moreover, the order of the inputs also matters: you should swap the start and end dates such that the start date precedes the end date.