I want to return data that is not between current date and the last 7 days.
My SELECT statement appears ok, but it is also returning the current day's data.
customer.id AS id,
customer.customer_id AS customer_id,
customer.name AS name,
customer.phone1 AS phone1,
customer.location_area AS location_area,
sales.post_date AS post_date
sales.customer_id = customer.customer_id
CAST( DATE_SUB(NOW(), INTERVAL 7 DAY) AS DATE )
CAST( NOW() AS DATE )
This problem is usually confusion about the different meanings of
DATE datatypes on the one hand and
DATETIME data types on the other.
1-April-2017 09:35. And, let's say you have a row in your
sales table with a
post_date value of
1-April-2017 08:20. Let's say your
post_date column has the data type
Then your WHERE clause looks like this after values are applied.
WHERE '2017-04-01 08:20' NOT BETWEEN CAST( '2017-03-25 09:35' AS DATE ) AND CAST( '2017-04-01 09:35' AS DATE )
CAST operations, we get.
WHERE '2017-04-01 08:20' NOT BETWEEN '2017-03-25' AND '2017-04-01'
Finally, when comparing a
TIMESTAMP to a
DATE value, the
DATE value is interpreted as having a time of midnight. So your query looks like this:
WHERE '2017-04-01 08:20' NOT BETWEEN '2017-03-25 00:00:00' AND '2017-04-01 00:00:00'
And, guess what?
'2017-04-01 08:20' is after
What you need is this:
WHERE NOT ( post_date >= CURDATE() - INTERVAL 7 DAY --on or after midnight 2016-3-25 AND post_date < CURDATE() + INTERVAL 1 DAY --before midnight 2016-04-02 )
You can't use
BETWEEN for this kind of comparison because you need
< for the end of the range, and
<= for the ends of all its ranges.
CURDATE() is much easier to read than
CAST(NOW() AS DATE).