Bug #567 LIKE does not work on DATETIME columns in 4.1
Submitted: 2 Jun 2003 7:14 Modified: 19 Mar 2004 7:32
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Any (all)
Assigned to: CPU Architecture:Any

[2 Jun 2003 7:14] SINISA MILIVOJEVIC
Description:

I have a query where I use a LIKE on a DATETIME field and it used to work
   i.e.: WHERE article_datetime LIKE '2003-05%'
but when we upgraded to 4.1 it stopped working (it didn't return any
results). In itself I find that increased restrictiveness to accomplish
no purpose. And then I found out that the same query on a DATE field *did*
work as expected. It works on a DATE but not on a DATETIME. Where's the
logic in that?

If possible, I would like to find someplace that has more details on the
nature of the change that was made to date/datetime checking. The changelog
for 4.1.0 isn't very informative, it only says that "DATE/DATETIME checking
is now a bit stricter to support the ability to automatically distinguish
between date, datetime, and time with microseconds."

Thanks,
Daniel DeLorme

How to repeat:
CREATE TALBE xx (xx datetime);
INSERT INTO xx VALUES ('1999-02-01'),('1999-02-03'),('2003-01-01');
SELECT xx FROM xx WHERE xx LIKE '1999-02-%';
[2 Jun 2003 8:04] MySQL Verification Team
DATETIME is integer .
[19 Mar 2004 7:32] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

see http://bugs.mysql.com/bug.php?id=2885