Inconsistent MySQL DATEDIFF behavior

Question

When I use this query:

SELECT COUNT(*) FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2

MySQL executes the query with no errors and I get the count of rows that have not been updated within the last 2 days. However, if I change the query like this:

SELECT * FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2

I get the following error:

#1305 - FUNCTION mydatabase.DATEDIFF does not exist

Any ideas why this is so?

Answer 1

Verify that you don't have a space between DATEDIFF and the bracket (.

Possibly you could also try SET sql_mode = "IGNORE_SPACE";

Also check this bugreport.

Answer 2

I've had a similar problem where it would not work on SELECT if I had not set the LIMIT.

Try to do something like:

SELECT * FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2 LIMIT 0, 10

It got corrected on a MySQL update. Try to update your MySQL version also.