Bug #26054 Unable to convert MySQL date/time value to System.DateTime using Microsoft Visua
Submitted: 3 Feb 2007 21:06 Modified: 15 May 2007 8:45
Reporter: Chang-Ping Hsiao Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Visual Studio Plugin Severity:S3 (Non-critical)
Version:1.0.2 and 1.1 OS:Windows (Windows XP Professional)
Assigned to: CPU Architecture:Any
Tags: qc

[3 Feb 2007 21:06] Chang-Ping Hsiao
In Microsoft Visual Studio 2005 Professional Edition, when I select Tools -> Connect to Database..., I am able to connect and see table information of a database, but when I try to "Browse or Edit Data", using both 1.0.2 and 1.1 (alpha), I get the following two error messages.

Unable to convert MySQL date/time value to System.DateTime.
Failed to reload document data! Document will be closed.

There are two columns designed as "date" and "datetime".  If there is no data for it, the default values 0000-00-00 and 0000-00-00 00:00:00 are used.  I don't know what happened.  This only happens in one database (schema); "Browse and Edit Data" for another table with datatime column on the same MySQL server does not have any problem.

How to repeat:
In Microsoft Visual Studio 2005 Professtional Edition
1. Select "Tools" menu
2. Select "Connect to database..."
3. Enter Server name, User name, password and Database name then click OK button
4. Double click on the data connection
5. Double click on Tables
6. Right mouse click on the table interested
7. Select :Browse and Edit Data"
8. MessageBoxes pop up.
[3 Feb 2007 21:06] Chang-Ping Hsiao
Error message

Attachment: capture1.GIF (image/gif, text), 8.73 KiB.

[3 Feb 2007 21:07] Chang-Ping Hsiao
Error message

Attachment: capture2.GIF (image/gif, text), 6.59 KiB.

[12 Mar 2007 18:32] Tonci Grgin

This report lacks basic info needed for verification:
 - MySQL server version
 - c/NET version
 - NET FW version
 - Is table in question checked for errors? If not, please do check it.
 - What is *exact* table structure that leads to a crash every time you tried above steps?

So far, I am unable to repeat this problem:
 - MySQL 5.0.38BK on WinXP Pro SP2 localhost
 - VS 2005 Pro
 - c/NET 5.0.3GA
 - Net FW 2.0
 - MySQL VS plugin 1.1.2-alpha
[19 Mar 2007 6:30] nirav jariwala
I am also having the same problem.
Any help will be really appriciated. I am having expiry date field in MySQL database and datagrid is posing this error if expiry date is blank.
[12 Apr 2007 23: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".
[14 Apr 2007 10:05] Tonci Grgin
Well, everybody's having problems but I don't see the info requested is posted.
Changing to "Can't repeat".
[14 May 2007 17:03] Wolfgang Schulze-Zachau
Samemproblem here.
Here are the details:

MySQL Version 4.1.11 (debian sarge 7)
Table definition:

CREATE TABLE `Shipment` (
  `id` int(11) NOT NULL auto_increment,
  `order_id` varchar(10) NOT NULL default '',
  `delivery_code` varchar(30) NOT NULL default '',
  `weight` double NOT NULL default '0',
  `weigh_date_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `batch_id` int(10) unsigned NOT NULL default '0',
  `cancelled` tinyint(1) unsigned NOT NULL default '0',
  `cancelled_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `orderid` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_ORDER` (`order_id`),
  KEY `IDX_BATCH` (`batch_id`),
  KEY `orderid` (`orderid`)

Then insert a row with:
insert into Shipment (id, order_id, orderid, delivery_code, batch_id) values (5, "AR500000", 500000, "GB12345678SD", 50);

then use the MySQL Connector/.Net and try and load a datatable with the statement "SELECT * FROM Shipment" and you will get the reported error message.
The ONLY way to get around the problem is to set the columns in the database to NULL and remove any existing default stamps with zeros. 
There are cases where this is not desirable or possible. In those cases the only other option are some really ugly type casts in the query (cast to string and then handle the value as a string in the code).

Any more details required, please ask and I will provide.

From my perspective this is a problem in the Connector/.NET. For date/time values of '0000-00-00' or similar it should return a DBNULL value to the DataTable in .NET, otherwise the Date/Time types in .Net come up with some funny values of their own (e.g. Date = 01/01/0001 etc.)

Wolfgang Schulze-Zachau
[14 May 2007 17:05] Wolfgang Schulze-Zachau
Ahem, some more information:

.NET Framework 1.1
Connector/.Net 1.0.7

Table has been checked for errors (this is our production database).

[14 May 2007 19:53] Tonci Grgin
Wolfgang thanks, at least some info I can work with...
[15 May 2007 8:45] Tonci Grgin
Hi all. This is not a bug but expected behavior. Please check manual under connect options and set "Allow Zero Datetime" to true, as on attached pictures, and the error will go away. I used Wolfgang's table.

 - MySQL server 4.1.23BK on Suse 10.0 host
 - WinXP Pro SP2 client with NET FW 2.0, c/NET 1.0.9
 - MS VS2005Pro
[15 May 2007 8:47] Tonci Grgin
Wolfgang's table opened for browsing / editing

Attachment: 26054-1.jpg (image/jpeg, text), 16.31 KiB.

[15 May 2007 8:47] Tonci Grgin
Setting advanced connection properties

Attachment: 26054-2.jpg (image/jpeg, text), 25.12 KiB.

[5 Jul 2013 21:45] Giuseppe Crescimanno
Sorry if this Comes 6 years too late... :)

Well I did as advised but am still running into the Exception.

Using MySql.Data 6.6.5 / MySql.Data.Entity 6.6.5 with Entity Framework 5 on .Net 4.5

Check out the Picture, thanks.
[5 Jul 2013 21:45] Giuseppe Crescimanno
Unable to convert MySQL date/time value to System.DateTime

Attachment: 05-07-2013 23-42-01.jpg (image/jpeg, text), 128.20 KiB.

[4 Sep 2014 9:51] Barry Cummins
Just to clarify a couple of things with this and I know its an old reported bug but I did find it useful when searching Google.

.NET 4.0 / 4.5 : MySQL 6.1

I was able to resolve this particular issue by setting the connection string property "Allow Zero Datetime = True".

However, this now introduces a new problem.  If you try serialise the data table returned by a query, it falls over.  Stating that there is an invalid data type (MySQL Date Time type).

See other bug #52141

So each resolution is contradictory to each other.  I would suggest this be resubmitted as a bug.

[4 Sep 2014 10:23] Barry Cummins
However, adding "Convert Zero Datetime=True" to the connection string seems to get over this hurdle.