MySQL Bugs: #28771: MySqlDataAdapter swaps MySqlDateTime values (Year, Month, Day)

Bug #28771	MySqlDataAdapter swaps MySqlDateTime values (Year, Month, Day)
Submitted:	30 May 2007 10:32	Modified:	5 Jun 2007 7:06
Reporter:	Jason Ureta	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.37	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
I have a function that uses the MySqlDataAdapter fill a DataSet with a query from a stored procedure. It returns the data successfully, which is only 1 row so far. One of the columns is a Date datatype and I'm trying to convert it to a DateTime .NET datatype. It's a valid date (2007-06-04). I tried the .ToString() function, but an error occured that's out of scope of this bug report. So I tried the following code:

DateTime NewDate = new DateTime(thisDate.Year, thisDate.Month, thisDate.Day, thisDate.Hour, thisDate.Minute, thisDate.Second);

thisDate is the MySqlDateTime datatype. It gave me an error. So I decided to step through it and as I got to that line, I noticed that Year = 6, Month = 4, and Day = 2007! In other words, the Year property is the Month, the Month property is the Day, and the Day is the Year! I know for sure that there's no month that has 2007 days in it, so this has got to be a bug. I haven't used Hour, Minute, or Second yet so they should all still be 0, but they might be affected as well.

I'm using Connecter/NET 1.0.9, MySQL 5.0.37, and Windows XP Professional SP 2.

How to repeat:
Create a table with a column using the Date datatype, and a stored procedure that returns a query on that table (thought it could probably be reproduced from a select statement directly). Then make a new .NET project. For the code, create a MySqlDataAdapter that calls the stored procedure or select statement, create a new DataSet, then use .Fill(DataSet) to fill the dataset. Put a breakpoint right after that statement and check the data. Year, Month, and Day will have mixed up values.

Suggested fix:
I have verified that the data is correct in the database. The values get mixed up after calling MySqlDataAdapter.Fill method. It's most likely a problem somewhere in MySqlDataAdapter. I've had no problems inserting or updating date fields.

Current Workaround:

DateTime NewDate = new DateTime(thisDate.Day, thisDate.Year, thisDate.Month, thisDate.Hour, thisDate.Minute, thisDate.Second);

It fits my needs for now...

Sorry for the trouble everyone. It seems that upgrading MySQL Server to 5.0.41 has fixed it. After installing 5.0.41, I confirmed it by uninstalling it and reinstalling 5.0.37 and the problem came back. Uninstalling it and reinstalling 5.0.41 fixed the problem. I looked through the changelog and there doesn't seem to be anything about this issue that was fixed, so I guess it was fixed as an unintended side-effect to a different bugfix.

Hi Jason and thanks for your report. Is it possible that:
 1) it was 5.0.42? If so, this could be duplicate of Bug#27590
 2) you changed c/NET
 3) you changed any configuration part and/or code?

1) I was definitely using Generally Available 5.0.41. Upgrading to that version from 5.0.37 fixed the problem.
 2) I used Generally Available 1.0.9. I used the binary directly. I didn't build it from source.
 3) During the tests I did with the upgrades, I didn't change any code.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Jason, after reviewing change logs (http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-41.html) I think this is what caused you problem to be fixed:
 - Conversion of DATETIME values in numeric contexts sometimes did not produce a double (YYYYMMDDHHMMSS.uuuuuu) value. (Bug#16546)
I'm closing the report now as the problem is solved.