Doing this works fine:

SELECT * FROM table WHERE foo < '2021-01-09 00:00:00'

But this one:

SELECT * FROM table WHERE DATE_ADD('foo', INTERVAL 5 DAY) < '2021-01-09 00:00:00'

results in

Warning: #1292 Incorrect datetime value: 'foo'

What could possibly be causing this?

(MariaDB 10.4.17)

  • The problem is that 2020 is not over. The correct value for 2021-01-09 is 2020-12-40. – Rick James Jan 9 at 17:31
  • @RickJames What do you mean? – IMB Jan 10 at 1:29
  • 1
    Just joking about how bad 2020 was and how the trauma has not finished, especially in the US. – Rick James Jan 10 at 1:30
  • @RickJames Whew and yeah.. – IMB Jan 10 at 7:14

You try to add to the string 'foo' 5 days.

Use backticks for column names

SELECT * FROM table WHERE DATE_ADD(`foo`, INTERVAL 5 DAY) < '2021-01-09 00:00:00'
  • Why not just foo? – Lennart Jan 9 at 11:56
  • foo is only a placeholder for the real column name, which we don't know. so to be on the save side, it is better to use backticks. But the basic problem, that the user added single quotes and mysql interprets these a stimg, which causes the problem and he has to use backticks instead of the single quotes – nbk Jan 9 at 12:12
  • I can't believe it's only that. Thanks! – IMB Jan 9 at 12:19
  • @nbk, In the first example foo is used and works. Reason for the string in the second example is unclear, but excessive use of quotes whether needed or not should IMO be discouraged. – Lennart Jan 9 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.