CODEWITHSUNDEEP
mysqlsql
punkbit
punkbit

Reputation: 7707

MySQL, get data between start and end date columns?

I've read a few similar questions then mine, where I could find queries that were pretty much the same I'm using. But I had to ask, because I would like to understand why this is not working:

I have the following data:

id  category_id     start_date  end_date    image   campaign_id     published
1   1               2011-07-05  2011-07-5   a.gif   3               1           
2   1               2011-07-01  2011-07-15  c.gif   3               1
3   37              2011-07-01  2011-07-04  d.gif   3               1

I expect to get rows 1 and 2, from this query:

SELECT id, category_id, start_date, end_date FROM categories_campaigns WHERE  start_date <= NOW() AND end_date >= NOW();

From what I've experienced, the only row returned is the second. Also this one, gives me the second,

SELECT category_id, start_date, end_date FROM categories_campaigns WHERE   end_date >= NOW();

The next one returns me all 3,

SELECT category_id, start_date, end_date FROM categories_campaigns WHERE   start_date <= NOW();

The datatype for both columns are DATE. So, my question is, how to solve this ? Why is this happening ? Or I've got an obvious error that I'm not finding on what to look for.

Upvotes: 2

Views: 8484

Answers (3)

punkbit
punkbit

Reputation: 7707

Working query

SELECT
    category_id, start_date, end_date
FROM
    categories_campaigns
WHERE
    start_date <= DATE(NOW()) and end_date >= DATE(NOW());

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

I suppose start_date and end_date are of date datatype. But NOW() returns a date-time value.

Use CUR_DATE() instead of NOW()

Upvotes: 0

panagdu
panagdu

Reputation: 2133

I think you could use this:

SELECT category_id, start_date, end_date FROM categories_campaigns WHERE left(now(),10) between start_date and end_date;

Upvotes: 0

Related Questions