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';
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.
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…
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.
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?
Super atvise.
Had the same error and got help form here
Best Regards Klaus From denmark
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
anyone know how to pass “Allow Zero Datetime=True” through nhibernate..?
you may need to add both of these options ….
Convert Zero Datetime=true;Allow Zero Datetime=True;
Try this
Database=empmgr;Data Source=localhost;User Id=root;Password=”;allow zero datetime=FALSE”
OK.. thx sob…
It’s solve my problem.. Thanks
Still usefull! 😀
Thanks for the solution
you can use ifnull(datetimefield,’0′) at your mysql select statement and it solve everything.
Danke glenn!
that was perfect for me!!!!!!!
Convert Zero Datetime=true;Allow Zero Datetime=True;
Great tips, this worked for me. Thanks!
i use this Below code but i got error (Unable to convert MySQL date/time value to System.DateTime) ones i debugging the error is mark .Fill(Table) so please help me to solve problem
Regards
Anil
Imports MySql.Data.MySqlClient
Public Class Form1
Public SConnection As New MySqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If SConnection.State = ConnectionState.Closed Then
SConnection.ConnectionString = “SERVER = localhost; USERID = root; PASSWORD = ; DATABASE = employee”
SConnection.Open()
End If
LoadPeople()
End Sub
Public Sub LoadPeople()
Dim sqlQuery As String = “SELECT * FROM tblemployee”
Dim sqlAdapter As New MySqlDataAdapter
Dim sqlCommand As New MySqlCommand
Dim TABLE As New DataTable
Dim i As Integer
With sqlCommand
.CommandText = sqlQuery
.Connection = SConnection
End With
With sqlAdapter
.SelectCommand = sqlCommand
.Fill(TABLE)
End With
For i = 0 To TABLE.Rows.Count – 1
With lvPeople
.Items.Add(TABLE.Rows(i)(“id”))
With .Items(.Items.Count – 1).SubItems
.Add(TABLE.Rows(i)(“NAME”))
.Add(TABLE.Rows(i)(“ICNO”))
.Add(TABLE.Rows(i)(“WPNOEXP”))
.Add(TABLE.Rows(i)(“PASSPORTNO”))
.Add(TABLE.Rows(i)(“PASSPORTEXP”))
.Add(TABLE.Rows(i)(“DOB”))
.Add(TABLE.Rows(i)(“SICNO”))
.Add(TABLE.Rows(i)(“GENEXPDATE”))
.Add(TABLE.Rows(i)(“HOTEXPDATE”))
.Add(TABLE.Rows(i)(“FINNO”))
End With
End With
Next
End Sub
End Class