date arithmetic - MYSQL appear error #1292 when delete record with in 7 day at 2021 first week

Question

i got a problem for MySQL , last week , i get the error when run this MySQL script

delete from calendar.schedule 
WHERE startdate >= DATE(NOW()) - 7 and status = 'ready'

it will display

#1292 - Incorrect datetime value: '20210100' for column 'startdate' at row 1" error .

my testing date is 2021/1/7 , if i change the Mysql script to

delete from calendar.schedule 
WHERE startdate >= DATE(NOW()) - 6 and status = 'ready' 

it will work normally. now, this code has no issues. but it will have bugs in the first week of the next year. anyone can help with this? Many Thanks!

Wilson

question from:https://stackoverflow.com/questions/65661141/mysql-appear-error-1292-when-delete-record-with-in-7-day-at-2021-first-week

Answer 1

Your issue is with this expression:

DATE(NOW()) - 7

When you try to subtract 7 from a date, MySQL converts the date to its integer representation (in this case I presume it was 20210107) and then subtract 7 from it, giving 20210100. It then tries to compare this to a datetime column and fails, since 20210100 is not a valid date. The code works when you use 6 because you end up with 20210101, which is valid. What you should be doing instead is subtracting an interval (see the manual) so that you use date arithmetic, not integer arithmetic:

CURDATE() - INTERVAL 7 DAY

Note that CURDATE() is equivalent to DATE(NOW())