How to fix “Unable to convert MySQL date/time value to System.DateTime” exception when using MySQL .NET Connector
Recently I exported a MySQL database (from the opensource osCommerce e-shop PHP-based platform) using phpMyAdmin (the one included in Plesk Control Panel) and then imported it to another instance of osCommerce, using again phpMyAdmin, this time running in an OpenSolaris WebStack Virtual Machine (hosted in VirtualBox).
However, it inserted zeroes (0) as date values (which is by default an invalid value for a date) instead of null. This resulted to both the Visual Studio database explorer’s table view and my application itself throwing “Unable to convert MySQL date/time value to System.DateTime” exception when using that database via MySQL Connector for .NET.
One can either fix those dates from phpMyAdmin or use a connection string that converts zero (invalid) DateTime to the minimum DateTime value (DateTime.MinValue).
This can be set at “Server Explorer” pane, right click the wanted MySQL connection and press “Modify Connection…”, then press “Advanced…”. Set “Convert Zero Datetime” and press OK. Then you can copy the resulting connection string from the text field at the dialog’s bottom and go from the Solution Explorer to Project Settings, select the "Settings" tab there and replace the previous value of the connection string that your application is using.
There’s a good change you haven’t placed yourself that application setting entry there, but a Visual Studio designer may have done this for you already and be using it in autogenerated code (you can see the autogenerated files by toggling the "Show all Files" button at the Solution Explorer pane). So make sure you don’t rename that setting entry and just replace its value or edit it appropriately (for example you may not want to have authentication values hardcoded in that string as the value copied from the dialog depicted below may have).
Note that at the "Advanced Properties" dialog you can right click a row and select remove to make it not show at the connection string and use the connector’s default value instead (e.g. can do this at "Allow Zero Datetime" above to make it not show as bold anymore).