Bug #9619 Cannot update row using DbDataAdapter when row contains an invalid date
Submitted: 4 Apr 2005 18:20 Modified: 29 Oct 2006 4:28
Reporter: David Christian Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.04 OS:Microsoft Windows (Windows XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[4 Apr 2005 18:20] David Christian
Description:
When a row has an invalid date, i.e. '0000-00-00', I am unable to post a change to any column in the row through the DataAdapter.  I   I receive the following exception:

MySql.Data.Types.MySqlConversionException
Unable to convert MySQL date/time value to System.DateTime

How to repeat:
Use the MySqlDataAdapter to post an update on an existing row that contains a date column with an invalid date '0000-00-00'.
[15 Apr 2005 7:52] [ name withheld ]
I have almost the same problem except this problem is for ALL the dates, not only the wrong one.

I have a column in my table which name is "date_insertion" and which type is "date"

i try to fill my dataset with :

strSQL = "SELECT *" & _ 
" FROM inserer, utilisateur, element" & _ 
" WHERE date_insertion >= (curdate() - INTERVAL 1 day)" & _ 
" AND inserer.id_user = utilisateur.id_user" & _ 
" AND element.id_element = inserer.id_element" & _ 
" ORDER BY date_insertion" 

Dim dadate As New MySqlDataAdapter(strSQL, objConn) 
Dim objDS As New DataSet 
Console.WriteLine(strSQL) 
dadate.Fill(objDS, "temp") ' <-------------------------- HERE IS THE ERROR ! 

I get the following error for any table with a date/time field

An unhandled exception of type 'MySql.Data.Types.MySqlConversionException' occurred in system.data.dll

Additional information: Unable to convert MySQL date/time value to System.DateTime
[15 Apr 2005 8:30] [ name withheld ]
in fact, there's only a problem when trying to access an field in which there is an invalid date like '0000-00-00'.
[16 May 2005 8:19] Vasily Kishkin
Tested on Win 2000 Sp4,  Microsoft Visual Basic .NET, Connector .NET 1.0.4
Test case is attached.
[16 May 2005 8:22] Vasily Kishkin
Tested case

Attachment: 9619.zip (application/x-zip-compressed, text), 6.19 KiB.

[28 Feb 2006 10:00] [ name withheld ]
I found here the solution:

http://mdid.org/mdidwiki/index.php?title=Unable_to_convert_MySQL_date/time_value_to_System...
[26 Sep 2006 22:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12579
[26 Sep 2006 23:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12580
[26 Sep 2006 23:06] Reggie Burnett
Fixed in 1.0.8 and 5.0.1
[29 Oct 2006 4:28] MC Brown
A note has been added to the 1.0.8 and 5.0.1 changelogs.