Problem with dates in 5.1.40

[semiller]

I recently upgraded to 4.1.8 to 5.1.40 in doing so the query:

select count(id) from TABLE where DateField >=2009-11-01' and DateField <='2009-11-05'

ignores the results from 2009-11-01 as if it was > instead of >= I have tested with the cast function and that works but do not want to rewrite every query to fix. Is there a solution or bug, or incompatible issue in 5.0 or 5.1 to this?

 

mysql [db]> select count(id) from TABLE where DateField>='2008-01-5';

+-----------+

| count(id) |

+-----------+

|        1 |

+-----------+

 

mysql [db]> select count(id) from TABLE where DateField>= cast('2008-01-5' as date);

+-----------+

| count(id) |

+-----------+

|        2 |

+-----------+

[fenway]

It's incompatible.

 

"Incompatible change: Beginning with MySQL 5.0.42, when a DATE value is compared with a DATETIME value, the DATE value is coerced to the DATETIME type by adding the time portion as 00:00:00. Previously, the time portion of the DATETIME value was ignored, or the comparison could be performed as a string comparison. To mimic the old behavior, use the CAST() function to cause the comparison operands to be treated as previously."