Why do double quotes influence result in compareing datetime?

Why do these two results dont return the same results?

SELECT foo FROM bar WHERE addtime > "2020-10-10" # returns several results

SELECT foo FROM bar WHERE addtime > 2020-10-10 # zero results

Can anyone explain why？

(addtime is datetime type.)

Answer

This does not do what you want:

where addtime > 2020-10-10

Unquoted 2020-10-10 is an arithmetic operation, which returns integer value 2000. You are then asking MySQL to compare it with a date, so it tries to convert the integer to a date, which yields null – so the comparison is false for all rows.

On the other hand, in the other expression, the literal date is enclosed within double quotes, and MySQL recognizes it as such.

I would recommend single quotes for literal dates and strings; although MySQL allows double quotes for that purpose, this deviates from the SQL standard, where double quotes stand for identifiers (such as column names or table names), for no value added. So:

where addtime > '2020-10-10'