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

Question

Hi i have a table in mysql database with with a column for date. It was working when i initially set it up. Using custom format in my form in visual studio. But i deleted all the columns in my table and then reordered them but the same columns. After then i start getting the error: {Unable to convert MySQL date/time value to System.DateTime}. I have set convert to zero time=true yet it is still showing i have also change the default column value to NULL yet it is still showing. Please help me. Thanks

Chetan Ranpariya · Answer

Do you have any data in table? The datetime column have NULL values in it? Can you share the output of simple sql query which selects the value from datetime column as it is? Something like select top 10 CreatedDate from mytable It might happen that the datetime format store by SQL server is not compatible with .NET. How are you trying to convert SQL date time to .NET datetime?

Afzaal Ahmad Zeeshan · Answer

Please show the table schema that you are having and the datatype of that column. It is hard to help you with very less information.

Ikechukwu Daniel · Answer

Thanks. Having worked initially. What could have gone wrong when i reordered the columns with the same column name but getting this error of Unable to convert MySQL date/time value to System.DateTime}. How do i go about correcting this?

Afzaal Ahmad Zeeshan · Answer

Basically all of the databases manage the dates and times internally -- they either have date or time. In many cases that is just a long data type holding milliseconds etc. You mentioned > i have also change the default column value to NULL yet it is still showing. Which is the main reason of the error a DateTime object in .NET framework is a structure and structures are not reference types and thus cannot be mapped to any values that are null. Make sure your columns have a value and then retry. The problem primarily is with MySQL tools for Visual Studio. One more thing just similar to NULL errors also make sure that you are not setting your column values to '0000-00-00 00:00:00' -- default values. Consider using a real date time value such as today and then see if that works correctly or not.