CODEWITHSUNDEEP
mysqldatediff
Geoff
Geoff

Reputation: 15

Inconsistent MySQL DATEDIFF behavior

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?

Upvotes: 1

Views: 3463

Answers (2)

Frankie
Frankie

Reputation: 25165

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.

Upvotes: 2

Yannick Motton
Yannick Motton

Reputation: 35971

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.

Upvotes: 3

Related Questions