CODEWITHSUNDEEP
c#mysqldatetimedateibatis
Gopi
Gopi

Reputation: 5877

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

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?

Upvotes: 28

Views: 96565

Answers (10)

Guilherme Bley
Guilherme Bley

Reputation: 376

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"

Upvotes: 1

Liam
Liam

Reputation: 5476

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.

Upvotes: 0

Rahul Sharma
Rahul Sharma

Reputation: 271

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

Upvotes: 2

Romain BARTOLO
Romain BARTOLO

Reputation: 1

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.

Upvotes: 0

user693858
user693858

Reputation: 61

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

Upvotes: 6

Leacam
Leacam

Reputation: 11

This worked for me:

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

Upvotes: 0

WeezHard
WeezHard

Reputation: 2022

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

Upvotes: 13

levefdsa
levefdsa

Reputation: 1326

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

Upvotes: 131

Dan
Dan

Reputation: 1176

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.

Upvotes: 2

Amgad Fahmi
Amgad Fahmi

Reputation: 4349

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

Upvotes: 5

Related Questions