Bug #16189 MySqlConversionException while processing datetime field in year 2006
Submitted: 4 Jan 2006 15:09 Modified: 12 Feb 2006 22:45
Reporter: Charles Haines Email Updates:
Status: No Feedback Impact on me:
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7 OS:Windows (Windows XP)
Assigned to: Bugs System CPU Architecture:Any

[4 Jan 2006 15:09] Charles Haines
While trying to perform a Fill function on a MySqlDataAdaptor with a datetime field in the year 2006 I am getting an error.  The error is "Unable to convert MySQL date/time value to System.DateTime."  This code which I have not changed since the begining of December 2005 has worked up until today when new information with dates of 2006 where entered.  The relavent portions of the code is as follows (this is C# running under Visual Studio 2005):

// grab the last current ID in the status table
da = new MySqlDataAdapter("select timestamp,Status.rfid_tag,gType,comments from Status, Gear where " +
"Gear.rfid_tag=Status.rfid_tag order by timestamp DESC, Status.rfid_tag ASC limit 50", dbConn);
// the following code causes the exception to be thrown

dbConn is of type MySqlConnection and is currently an active connection to the database.  ds is of type DataSet and is initialized and working.  If I change the SQL in the MysqlDataAdaptor to timestamp < "2005-12-31 12:00:00", the above code works as expected and no exception is thrown.  I have verified that all of the dates in the datetime field is a correct date and time.

How to repeat:
Create a MySqlConnection to a mysql database using C# in Visual Studio 2005 under the .NET 2.0 Framework.

Create a MySqlDataAdaptor with the following SQL
"select timestamp,Status.rfid_tag,gType,comments from Status, Gear where Gear.rfid_tag=Status.rfid_tag order by timestamp DESC, Status.rfid_tag ASC limit 50" where the table schema is as follows:
| Field     | Type             | Null | Key | Default | Extra          |
| ID        | int(10) unsigned |      | PRI | NULL    | auto_increment |
| rfid_tag  | varchar(24)      | YES  | MUL | NULL    |                |
| timestamp | datetime         | YES  |     | NULL    |                |
| comments  | text             | YES  |     | NULL    |                |

Create a new DataSet.

Call Fill on the MySqlDataAdaptor passing the DataSet as the parameter.

Insert timestamps into the database that have the year as 2006.

Try and run the code.  The exception occurs.
[12 Jan 2006 22:45] Vasily Kishkin
Sorry...I was not able to reproduce the bug on my test case. Could you please create and attach some simple test case ?
[13 Feb 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[27 Sep 2007 20:03] arash archer
add this to the connection string :
Allow Zero Datetime=true