Unable to convert MySQL date/time value to System.DateTime
Apr 16
After switching from MySQL ODBC driver to the .NET connector I have been getting this error: Unable to convert MySQL date/time value to System.DateTime.
This is when a DATETIME or DATE field contains an invalid value. It turns out that 0000-00-00 which I have used as default value in a DATE column is considered an invalid date. Instead NULL should be used when date/time is not set.
The one solution I found is to add Allow Zero Datetime=True to the connection string, this allows for 0000-00-00 00:00:00 in a DATETIME column. Just add it at the end of your connection string so it looks something like this:<add key="connectionString" value="Database=your_db;Data Source=localhost;User Id=root;Password=password;Allow Zero Datetime=True;"/>
This however did not seem to work 100% for my code.
In VB.NET I was using this piece of code to check if the valid_until field (of type DATE) is set or not:If ("" & myDataReader("valid_until")) = "" Then ...
I was concatenating to an empty string istead of using .ToString() because this (if I remember it correctly) would catch both NULL and 0000-00-00 values (both would evaluate to empty string).
Now instead I got this error message: Conversion from type ‘MySqlDateTime’ to type ‘String’ is not valid.
With ODBC, 0000-00-00 used to evaluate to empty string, but using the .NET connector with the connection string work around it returns the actual value, so I have to check for the zeroes instead. So, the solution to my problem is of course to use the proper .ToString function and check for both “0000-00-00″ and empty string returned for null value (or even better – use the proper IsDBNull function).
Another way besides using the connection string workaround is of course to fix the data.
- First check the data definitions in your databases to make sure it does not default to 0000-00-00 (for data type DATE) or 0000-00-00 00:00:00 (for data type DATETIME). I’m still running MySQL server version 4 but I think that on version 5, using 0000-00-00 as default value is no longer valid.
- Then update the data. An SQL statement to update a column of type DATE would typically look like this for a column named valid_until:
UPDATE your_table SET valid_until=null WHERE valid_until LIKE '0000-00-00';
Apr 18, 2007 @ 13:02:00
I’ve been having this problem too… fixing the data isn’t necessarily the solution, though, because sometimes I don’t want to fill in the date just yet. For instance, I’m writing a time logging program for a company to track clock ins and clock outs for employees. Well, if someone has clocked in, I don’t want to record a clock out time yet until they do! It would be nice if I didn’t have to record a default value.
Apr 18, 2007 @ 13:37:00
Hi Edelman,
The solution in your case is to set the field to NULL. The lesson is to not use 0000-00-00.
Jan 24, 2009 @ 05:47:00
Thanks it really working on me this code: Allow Zero Datetime=True….
My problem is i forget to Test my Connection string every time to running my program…
Nov 06, 2009 @ 17:53:13
I got this error while accesing a data table that does not contain a date or datetime field. The table just has four varchar fields.
The “Allow Zero Datetime=Ture” works but it would be good to know why when there isn’t any Datetime data.
Nov 11, 2009 @ 05:40:25
That’s a good solution to add “Allow Zero Datetime=True” to connection string. But I got longer time delay to get the data and my application seems to be hang up. Is there any solution for this without change the field to NULL?
Mar 03, 2010 @ 22:16:59
Super atvise.
Had the same error and got help form here
Best Regards Klaus From denmark
Apr 16, 2010 @ 10:34:26
I used a small query trick to solve that.. how read the date as string.
“… concat(date_x,”) as tgl_date_x …”
it will read as string.. and solve “Unable to convert MySQL date/time value to System.DateTime” problem.. ^_^
hope usefull..
Regards,
Yupi Sugianto, S.Kom
Jul 14, 2010 @ 15:53:38
anyone know how to pass “Allow Zero Datetime=True” through nhibernate..?
Nov 29, 2010 @ 21:21:19
you may need to add both of these options ….
Convert Zero Datetime=true;Allow Zero Datetime=True;
Feb 14, 2011 @ 17:13:07
Try this
Database=empmgr;Data Source=localhost;User Id=root;Password=”;allow zero datetime=FALSE”
Mar 01, 2011 @ 08:58:04
OK.. thx sob…
Apr 07, 2011 @ 08:08:24
It’s solve my problem.. Thanks
May 10, 2011 @ 14:56:21
Still usefull!
Thanks for the solution