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

Question

I am using ibatis and C#. i get a result from a select query that has CreatedDate as one of the field. The Datatype of CreatedDate in Mysql is Date. I assign the result set of the select query to a Ilist< DeliveryClass>.

Here the DeliveryClass CreatedDate as DateTime. When i run the application, i get Unable to convert MySQL date/time value to System.DateTime. What could be the problem?

Confidence

Answer 1

MySqlConnection connect = new MySqlConnection("server=localhost; database=luttop; user=root; password=1234; pooling = false; convert zero datetime=True");

Adding convert zero datetime=True to the connection string will automatically convert 0000-00-00 Date values to DateTime.MinValue().

that's SOLVED

Bandoleer

This is better solution than chosen. – Saturnian

This one worked fine for me, after updating the connection string in the appSettings.json file. Thank you @Klors. – Recreation

Answer 2

Adding "convert zero datetime=True" to the connection string solved my problem.

<connectionStrings>   <add name="MyContext" connectionString="Datasource=localhost;Database=MyAppDb;Uid=root;Pwd=root;CHARSET=utf8;convert zero datetime=True" providerName="MySql.Data.MySqlClient" /> </connectionStrings>

Regards PS

Boatload

Answer 3

I solved my problem by setting the column's default value as null data rather than using 0000-00-00 00:00:00:

update table set date = null

Genipap

Answer 4

The problem in the format, actually mysql have a different format (yyyy-mm-dd) for the date/time data type and to solve this problem use the mysql connector library for .net from here http://dev.mysql.com/downloads/connector/net/ it will give other data type for the date/time called MysqlDateTime

or you can format the date/time data in your sql statement using DATE_FORMAT(date,format) you can get more details from here http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

but i don't recommend this because you will loose the power of date/time data type (for example you can't compare) because now you will convert it to string but i think it will be useful in the reports

Admeasurement

On updating the connector to the latest didn't solve my problem – Confidence

the connector it self will not solve your problem it depends how you are retrieving your data ?? for example i used my generation doodads template i override the retrieving property for the datetime (reading the value from the datatable => convert it from mysqldatetime to .net datetime) how u retrieve your data ? – Admeasurement

Dates have no format, they are binary values. Formats come into play only when parsing text containing date literals. Which shouldn't be used, as they introduce conversion errors and the possibility of SQL injection – Shannon

Answer 5

It could be outside the range of a DateTime object. I've seen that a couple of times. Try changing the sql to return the current date instead of your column and see if it comes through ok.

Jura

Answer 6

You need to do the simple change with the connection string you added for MySql database. i.e CONVERT ZERO DATETIME = TRUE

<add name="NAMEOFYOURCONNECTIONSTRING" connectionString="server's HOSTNAMEorIP(i.e Localhost or IP address);user id=USER_ID(i.e User ID for login Database);Pwd=PASSWORD(i.e User Password for login Database);persistsecurityinfo=True;database=NAMEOFDATABASE;Convert Zero Datetime=True" providerName="MySql.Data.MySqlClient"/>

Panier

Please elaborate properly. – Brice

@DarpanSanghavi Hope you can now understand easily. – Panier

Answer 7

Have two possibilities.

1 -> Cast all of the DateTimes '0000-00-00' to NULL.

This include in 'SELECT' querys. Example : (if any row returned has a DateTime equals a '0000-00-00', if not, it's not necessary.)

 SELECT 
DATE_FORMAT(DATEZERO,'%Y-%m-%d') DATEZERO ## CAST TO STRING
    FROM TABLE;

2 -> Add param in sql connection:

"convert zero datetime=True"

Stagehand

Answer 8

This worked for me:

SELECT 
    IF(tb.Date1 = '0000-00-00 00:00:00', NULL, tb.Date1) AS ValidDate
FROM MyTable AS tb

Extrabold

Answer 9

One thing who works too is changing your reader action. I was having this problem when I wrote

string myvar = reader.GetString(0);

Then I used to write this

object myvar = reader.GetValue(0);

And no more error.

Kurth

Answer 10

This issue happened to me when a date column in my table had been set to 0 rather than null - there was a bug in my code that.

Looking at the table I could see that the field had been set to a date of 000-00-00 - once I had reset the offending field to null the issue went away.

Blaeberry