Bug #7534 Empty DateTime in database creates unhandled exception
Submitted: 25 Dec 2004 15:33 Modified: 5 Jan 2005 18:27
Reporter: Claus Fristrup Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.3 OS:Microsoft Windows (Windows XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[25 Dec 2004 15:33] Claus Fristrup
Description:
A DateTime value of "0000-00-00 00:00:00" can not be converted to a System.DateTime.

When trying the following error occurs:
An unhandled exception of type 'MySql.Data.Types.MySqlConversionException' occurred in system.data.dll

Additional information: Unable to convert MySQL date/time value to System.DateTime

How to repeat:
Create table MyTest(mydate datetime not null, id int not null);
insert into mytest set id = 1;

C#:
data = new DataTable();
da = new MySqlDataAdapter("SELECT * FROM MyTest" , conn );
cb = new MySqlCommandBuilder( da );
da.Fill( data );//THROWS AN EXCEPTION
dataGrid.DataSource = data;
[5 Jan 2005 18:27] Reggie Burnett
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is expected behavior.  The reason is that .NET cannot hold a zero datetime in the DateTime struct.  The default behavior is to throw a MySqlConversion exception if an illegal datetime value is found.  To override this, you can add "allow zero datetime=yes" to your connection string.  This causes MySqlDataReader.GetValue to return a MySqlDateTime object instead of a DateTime object.  There are significant issues related to this issue so you should think about your database structure.  One of the best ways of dealing with this is to not allow illegal datetime values in your database.