MySQL strangeness with dates and BETWEEN

I'm running a query to get rows that fall between certain dates (the current date and 7 days ago for this example).

I tried writi开发者_如何转开发ng it as:

SELECT * 
FROM faulttracker.ft_v_cases 
WHERE DATE(cs_created) BETWEEN DATE(NOW()) AND DATE(NOW()-INTERVAL 7 DAY) 
ORDER BY cs_created DESC;

but it returned 0 rows. I couldn't understand why it didn't work, and I tried rewriting it as:

SELECT * 
FROM faulttracker.ft_v_cases 
WHERE DATE(cs_created) <= DATE(NOW()) 
AND DATE(cs_created) >= DATE(NOW()-INTERVAL 7 DAY) 
ORDER BY cs_created DESC;

which DID work.

Why did the first one return 0 rows but the second one work as expected? As far as I can see they should be functionally equivalent.

cs_created is a datetime.

According to the documentation, BETWEEN expects the following format:

 expr BETWEEN min AND max

In your first example, you are putting the min value last.

Try using:

SELECT * 
FROM faulttracker.ft_v_cases 
WHERE DATE(cs_created) BETWEEN DATE(NOW()-INTERVAL 7 DAY) AND DATE(NOW()) 
ORDER BY cs_created DESC;

I'm pretty sure you can just as well use:

WHERE cs_created >= CURDATE() - INTERVAL 7 DAY 
  AND cs_created <  CURDATE() + INTERVAL 1 DAY

This should return same results, but also allow the query to use index on cs_created.

Your condition should be -

WHERE DATE(cs_created) BETWEEN DATE(NOW()-INTERVAL 7 DAY) AND DATE(NOW())

The lower date value should be on left side when using Between.

You must always have the range working as BETWEEN smaller AND larger; the treatment is the same regardless of whether you write:

x BETWEEN smaller AND larger
x >= smaller AND x <= larger

Hence, your code using BETWEEN needs to be written as:

SELECT * 
  FROM faulttracker.ft_v_cases 
 WHERE DATE(cs_created) BETWEEN DATE(NOW() - INTERVAL 7 DAY) AND DATE(NOW())
 ORDER BY cs_created DESC;

That covers the last 8 days, of course, since the range with BETWEEN/AND is inclusive.

have you tried putting it like this:

SELECT * FROM faulttracker.ft_v_cases WHERE CAST(cs_created AS DATE) BETWEEN CAST(NOW() AS DATE) AND CAST(DATE(NOW()-INTERVAL 7 DAY) AS DATE) ORDER BY cs_created DESC;

the manual states:

to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.