CODEWITHSUNDEEP
mysqldatetime
Ismail Gunes
Ismail Gunes

Reputation: 558

Why my date is written 0000-00-00

I have a table where the date column name is defined as proddate and defined as DATE.

Here is the code I write the value into table:

cmd.Parameters.Add("@Mydate", MySqlDbType.DateTime).Value = Convert.ToDateTime(MyArray[4]).ToString();

This gives a result of 0000-00-00

When I change it to

cmd.Parameters.Add("@Mydate", MySqlDbType.DateTime).Value = Convert.ToDateTime(MyArray[4]);

The result is correct. eg: 2013-11-14

However few lines down I have this code

cmd1.Parameters.Add("@date", MySqlDbType.DateTime).Value = Convert.ToDateTime(MyArray[4].ToString());

This gives no error. I get the correct result in table

And few lines after I have this code in the same method:

cmd3.Parameters.Add("@Mydate", MySqlDbType.DateTime).Value = MyArray[4].ToString();

This gives no error too

The last two lines I did the mistake for testing. But the columns of 2 last exemples are defined as DATE format

Any idea ? Or Am I welcome in the mystery world of Mysql ?

PS: I use MYSQL CONNECTOR 6.7.4.0 .NET3.5 and C# 2008 Express. In my server MYSQL is 5.0

Upvotes: 0

Views: 452

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1502106

In the first version, you're converting your value to a DateTime and then to a string, using the default format for a DateTime value. MySQL is then trying to parse that, and presumably failing.

In the second version, you're parsing your value as a DateTime, and supplying it to MySQL directly. This is the best approach, although you need to be careful about the input format you're using.

The third version is like the second, just with an explicit ToString call. If MyArray is of type string[], that's basically a no-op.

The fourth version is providing the string input directly to MySQL, which is presumably able to parse it itself. I would suggest avoiding that form though - parse it in the .NET code, where you have much more control.

Upvotes: 1

Related Questions