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?

score 125 · Accepted Answer · edited May 17 '13 at 23:15

125

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

edited May 17 '13 at 23:15

Klors

2,665
2
25
42

answered May 17 '13 at 22:19

levefdsa

1,266
1
9
2

2

This is better solution than chosen. – TuralAsgar Jun 09 '18 at 13:41
This one worked fine for me, after updating the connection string in the appSettings.json file. Thank you @Klors. – marsahllDTitch Dec 06 '18 at 10:40

score 13 · Answer 2 · answered Sep 10 '13 at 18:16

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

score 6 · Answer 3 · edited May 10 '18 at 11:03

6

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

edited May 10 '18 at 11:03

4b0

21,981
30
95
142

answered Sep 19 '11 at 18:17

user693858

61
1
2

Amgad Fahmi · Answer 4 · 2010-05-29T11:55:54.167

5

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

edited May 29 '10 at 11:55

answered May 29 '10 at 11:45

Amgad Fahmi

4,349
3
19
18

On updating the connector to the latest didn't solve my problem – Gopi May 29 '10 at 12:11
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 ? – Amgad Fahmi May 29 '10 at 12:35
1

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 – Panagiotis Kanavos Jan 28 '20 at 13:50

score 2 · Answer 5 · answered May 30 '10 at 04:15

2

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.

answered May 30 '10 at 04:15

Dan

1,176
1
9
10

Rahul Sharma · Answer 6 · 2018-10-19T03:13:37.770

1

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"/>

edited Oct 19 '18 at 03:13

answered May 10 '18 at 10:18

Rahul Sharma

241
2
6

Please elaborate properly. – Darpan Sanghavi May 10 '18 at 10:39
@DarpanSanghavi Hope you can now understand easily. – Rahul Sharma May 10 '18 at 10:40

score 1 · Answer 7 · answered Apr 28 '22 at 16:52

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"

score 0 · Answer 8 · edited Mar 13 '18 at 18:22

0

This worked for me:

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

edited Mar 13 '18 at 18:22

Graham

7,431
18
59
84

answered Mar 13 '18 at 18:00

Leacam

11
2

score 0 · Answer 9 · answered Jun 05 '18 at 08:46

0

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.

answered Jun 05 '18 at 08:46

Romain BARTOLO

1

score 0 · Answer 10 · answered Jul 15 '20 at 07:20

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.

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

10 Answers10

Linked