MySql How to use DATE_SUB in Between clause

Question

I am having problem while using DATE_SUB for DateTime fields. I want to use query like this:

SELECT
    * 
FROM 
    SellBySalesman 
WHERE 
    userid=37 and 
    sellingDate BETWEEN CURDATE() AND DATE_SUB(CURDATE(), INTERVAL 7 DAY)

Answer 1

I am not sure what error you are getting, but this will not work. The reason is that the arguments in your BETWEEN clause are reversed. BETWEEN requires the first argument to be the min and the second to be the max. DATE_SUB will subtract the interval from the date provided making it less than the current date.

Try something like this:

SELECT
    * 
FROM 
    SellBySalesman 
WHERE 
    userid=37 and 
    sellingDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()

Answer 2

Your BETWEEN values are backwards.

You need to have the smaller value first: "BETWEEN 1 and 10" not "BETWEEN 10 and 1".

So, in your case, you want "BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and CURDATE()"

Answer 3

You have to invert the order of between like this:

SELECT
    * 
FROM 
    SellBySalesman 
WHERE 
    userid=37 and 
    sellingDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE();