Mysql compare if date is at least two day away from now

Question

I have a table, has field named date with type date.

I have a row with date=2021-08-11, I want to perform an update, if table.date is more than 2 days from current, update it, else skip it.

update tableA set count = 10 where id=26 and date > (DATE(NOW()) + INTERVAL 2 DAY);
//date is 2021-08-11.

the query returns: Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

Can someone give me some help!? Any help would be greatly appreciated!

Answer 1

Well I don't see any problem -- why are you expecting the row updated?

Today is 2021/8/9, add 2 days is 2021/8/11

Your date is 2021/8/11, so date > 2 days later 2021/8/11 > 2021/8/11 is false.

You need to add some data as 2021/8/12 or later to make the updat happen.

Answer 2

Try using DATE_ADD and enclose your date variable inside DATE(). And you might want to use CURDATE instead of DATE(NOW()) - but they are also the same, just to shorten your query.

UPDATE tableA set count = 10 
WHERE id=26 and DATE(`date`) > DATE_ADD(CURDATE, INTERVAL 2 DAY);