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
- adding the parameter ‘Allow Zero Date=true;’ to your connection string (worked for me)
- setting the values to a none zero date (kind of obviously)
- 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