| Bug #6429 | Zero date "0000-00-00" is returned wrong when filling Dataset | ||
|---|---|---|---|
| Submitted: | 4 Nov 2004 12:33 | Modified: | 9 Nov 2004 17:34 |
| Reporter: | Matej Simikic | ||
| Status: | Closed | ||
| Category: | Connector/Net | Severity: | S1 (Critical) |
| Version: | 1.0.1 beta2 | OS: | Microsoft Windows (Windows XP SP2) |
| Assigned to: | Reggie Burnett | Target Version: | |
[4 Nov 2004 14:49]
Matej Simikic
I have corrected this by adding a line:
mValue = DateTime.ParseExact( "0001-01-01", "yyyy-MM-dd",
System.Globalization.DateTimeFormatInfo.InvariantInfo );
in MySQLDateTime.cs
ParseMySQL now it looks like:
case MySqlDbType.Date:
if (s == "0000-00-00")
{
isZero = true;
mValue = DateTime.ParseExact( "0001-01-01", "yyyy-MM-dd",
System.Globalization.DateTimeFormatInfo.InvariantInfo );
}
else mValue = DateTime.ParseExact( s, "yyyy-MM-dd",
System.Globalization.DateTimeFormatInfo.InvariantInfo );
break;
now i get same date as in MySQLDataReader (1.1.0001 0:00:00)
then i just check for 0001 and if found i print an empty string
Public Function MyDate(strDate as string) as String
Dim datum(2) As String
If strDate Like "*0001*" Then
MyDate = ""
Else
datum = Split(strDate, " ")
MyDate = datum(0)
End If
Return MyDate
End Function
[9 Nov 2004 17:34]
Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
I believe I have fixed this. The issue of zero datetimes is difficult, especially when
you consider different cultures. I have added a test case that tests this situation and
passes.
Description: I use: - MySQL Server 4.1.7 - WIndows XP SP2 - Connector / .NET 1.0.1 beta 2 - Visual Studio 2002 By using MySQLDataReader: Returned Non-Zero dates are formatted "YYYY-MM-DD" and Zero date "0000-00-00" is returned correctly as "0000-00-00". By using MySQLDataAdapter: Filling only one record in Dataset: Returned Non-Zero dates are formatted "D.M.YYYY 0:00:00" (windows default) and Zero dates "1.1.0001 0:00:00" (this is strange already) Filling multiple records in Dataset: While there is a Zero date it returns "1.1.0001 0:00:00" but if there is a Non-Zero Date, then all following Zero dates are returned as last Non-Zero date value. How to repeat: Make a table "MyTable" with columns "id" type INT (autoincrement) and "MyDate" type Date Insert into MyDate column following dates: 0000-00-00 0000-00-00 2001-11-05 0000-00-00 0000-00-00 in VB.NET use following code: Dim DS as New Dataset() Dim i as Integer Dim MyAdapter As New MySqlDataAdapter("SELECT MyDate FROM MyTable ORDER BY id ASC", MyConn) MyAdapter.Fill(DS, "MyTable") MyAdapter.Dispose() MyAdapter = Nothing For i = 0 to DS.Tables(0).Rows.Count-1 Debug.Write(DS.Tables(0).Rows(i).Item("MyDate")) Next In this case it returns: 1.1.0001 0:00:00 1.1.0001 0:00:00 5.11.2001 0:00:00 5.11.2001 0:00:00 5.11.2001 0:00:00