Unable to convert MySQL date/time value to System.DateTime

Question

I get this error:

Unable to convert MySQL date/time value to System.DateTime

while I am trying to fetch the data from a MySQL database. I have the date datatype in my MySQL database. But while retrieving it into my datatable, it get the error above.

How can I fix this?

Solution 1

If I google for "Unable to convert MySQL date/time value to System.DateTime" I see numerous references to a problem accessing MySQL from Visual Studio. Is that your context?

One solution suggested is:

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.

Reference: http://bugs.mysql.com/bug.php?id=26054

Solution 2

You must add Convert Zero Datetime=True to your connection string, for example:

server=localhost;User Id=root;password=mautauaja;Persist Security Info=True;database=test;Convert Zero Datetime=True

Solution 3

i added both Convert Zero Datetime=True & Allow Zero Datetime=True and it works fine

Solution 4

Pull the datetime value down as a string and do a DateTime.ParseExact(value, "ddd MMM dd hh:mm:ss yyyy", culture, styles); You would just need to set the date format up for the date you are returning from the database. Most likely it's yyyy-MM-dd HH:mm:ss. At least is is for me.

Check here more info on the DateTime.ParseExact

Solution 5

Let MySql convert your unix timestamp to string. Use the mysql function FROM_UNIXTIME( 113283901 )

View more solutions

Comments

[jp2code]

Thanks! FYI: This goes on the MySQL connection string - not the SQL Server connection string.

[Bimal Poudel]

I suggest not to use '0000-00-00 00:00:00' as your data in date/time columns. Rather use a real date values in your data - if you are using .net. However, MySQL can handle any dates. You should scan through all your date/time values.

[Naila Akbar]

saved my time. Worked like a charm

[Drako]

+1 - Perfect! I had zero datetimes because when the column wasn't fill write on table MySQL the zero datetime. I prefer change it from now for 0970-01-01.Thanks you so much