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:
<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';

17 comments

  1. 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.

  2. Hi Edelman,
    The solution in your case is to set the field to NULL. The lesson is to not use 0000-00-00.

  3. 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…

  4. 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.

  5. 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?

  6. 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

  7. 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

Leave a Reply to Poly Cancel reply

Your email address will not be published. Required fields are marked *