mysql WHERE '2010-08-24 11:59:59' BETWEEN c.start_date AND c.end_date;
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_da开发者_运维知识库te
(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.
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.
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
精彩评论