Unable to convert MySQL date/time value to System.DateTime
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:
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:
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.
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';
posted by Max Flodén at
Monday, April 16, 2007
2 Comments:
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.
Hi Edelman,
The solution in your case is to set the field to NULL. The lesson is to not use 0000-00-00.
Post a Comment
Links to this post:
Create a Link
<< Home