.Net Exception: Unable to convert MySQL date/time value to System.DateTime

When using DataAdapter.Fill()

I encountered the exception “Unable to convert MySQL date/time value to System.DateTime” while I was implementing MySQL support to our .Net data access component.When return the results(into a DataTable) from a select statement which include a date/time column that has a 0/0/0000 00:00:00 value i.e. you inserted zero.

You can avoid this exception either by 

1. adding the parameter ‘Allow Zero Date=true;’ to your connection string (worked for me)
2. setting the values to a none zero date (kind of obviously)
3. setting the values to null rather than zero.

solutions 2) and 3) are probably more suited for new databases where as 1) if you are planning on migrating a bunch of data from else where.

When implicitly conversting / cast  a Datetime DataTable column to string

Once I got past the above I hit the exception “Conversion from type ‘MySqlDateTime’ to type ‘String’ is not valid” while trying to implicitly convert the zero datetime value (i.e. 0/0/0000 00:00:00) in the DataTable date column.

I solved this by using the .ToString.

Worth mentioning that this exact code run against MS SQL Server and Oracle Database without encountering the above issues.

Thanks to Aleksandar’s Blog
http://vucetica.blogspot.com/2009/01/unable-to-convert-mysql-datetime-value.html

and Tjitjing Blog
http://blog.tjitjing.com/index.php/2007/04/unable-to-convert-mysql-datetime-value.html