Bug #12520 DATETIME Default Value 0000-00-00 00:00:00 not returning correct thru ODBC
Submitted: 11 Aug 2005 12:53 Modified: 3 May 2007 21:58
Reporter: Marvin Eads Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.11-2 OS:Windows (Win2003 Server)
Assigned to: Jim Winstead CPU Architecture:Any
Tags: datetime

[11 Aug 2005 12:53] Marvin Eads
Description:
When pulling a datetime field thru the ODBC with default values of 0000-00-00 00:00:00, I get return values of 0002-11-30 00:00:00.0.  I am using ColdFusion 6.1 as my front end and Win03 Server with MySQL 4.1.12a-nt.

How to repeat:
SELECT c.complaintacknowledged, c.complaintresolved, c.complaintclosed, 
	c.assignedto, s.state, s.firstname, s.lastname, s.phonenumber, 
	c.trackingnumber, c.facilitytype, c.type, c.originator
FROM cfs_customers s, cfs_complaints c
WHERE c.complaintkey IN(SELECT Max(complaintkey) AS pk FROM cfs_complaints GROUP BY trackingnumber)
AND c.customerkey=s.customerkey

That is the query I am running.  My datetime fields are complaintacknowledged, complaintresolved, and complaintclosed.

Suggested fix:
I assume this is an issue with the ODBC connector since the values return correct with Query Browser.
[11 Aug 2005 20:37] Jorge del Conde
I was able to reproduce this bug using MS Access & ASP.
[29 Sep 2005 9:09] Andrew Dixon
We have the same issue with MySQL 4.0.21 on Win2003 and Coldfusion MX 7. We have tried using the build-in driver, the JDBC drive and the ODBC driver. Could it be a Win2003 issue?
[24 Oct 2005 20:59] Peter Harvey
Please try Connector/ODBC v3.51.12 from www.mysql.com and let us know if this has either fixed or not fixed the problem. c/odbc v3.51.12 has some fixs regarding date/time data type.
[11 May 2006 9:28] Elizalde Baguinon
I have the same problem too. More often datetime columns with 0000-00-00 00:00:00 value won't return records . ADO returns EOF. I'm currently using Connector 3.51.12 on Visual Basic 6.
[4 Apr 2007 6:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23747
[3 May 2007 21:58] Jim Winstead
I've added a regression test for this bug, which no longer appears to happen, to the test suite.