0

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)

4
  • The problem is that 2020 is not over. The correct value for 2021-01-09 is 2020-12-40.
    – Rick James
    Jan 9, 2021 at 17:31
  • @RickJames What do you mean?
    – IMB
    Jan 10, 2021 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, 2021 at 1:30
  • @RickJames Whew and yeah..
    – IMB
    Jan 10, 2021 at 7:14

1 Answer 1

2

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'
4
  • Why not just foo? Jan 9, 2021 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, 2021 at 12:12
  • I can't believe it's only that. Thanks!
    – IMB
    Jan 9, 2021 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. Jan 9, 2021 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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