Bug #17022 Problems converting "blank" dates (0000-00-00 00:00:00)
Submitted: 1 Feb 2006 20:56 Modified: 8 Feb 2006 3:01
Reporter: Tom Wilson Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.07 OS:Microsoft Windows (Win XP)
Assigned to: CPU Architecture:Any

[1 Feb 2006 20:56] Tom Wilson 
Description:
Any time the connector tries to read a date that has the Zero value (0000-00-00 00:00:00), I get the following error message:

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

How to repeat:
create a table with a date field.
set the date value to 0000-00-00 00:00:00

in vb, use the adapter.fill to fill a dataset.

Suggested fix:
when reading the DATETIME field, convert the Zero date value to the .Net equivalent: #12:00 AM#. Likewise, when saving #12:00 AM# back to a MySql table, convert back to the My SQL Zero Date value.
[1 Feb 2006 21:18] Tom Wilson 
found the problem code....  in MySqlDateTime.cs, I changed the function GetDateTime to read as follows:

/// <summary>Returns this value as a DateTime</summary>
public DateTime GetDateTime()
{
      if(year==0 && month==0 && day==0)
        return new DateTime();  // this should return a Blank date
...

this returns a blank date (#12:00 AM#) when the table has a Zero date stored in it. This isn't a perfect solution, but since .Net has no provisions for a "null date" (that I know of), this is the next-best solution.

In all of my projects I define a constant, called "NullDate" and a function to output string versions of dates. It just does a check against NullDate and outputs "--/--/--" if the datetime fed in is #12:00 AM#.

As another option, why not leave the data coming off the database as a MySQL Date? It's easy enough to convert if necessary, but if the data is being used primarily for printing and inputting dates, all the functionality is already in the MySqlDateTime object.
[8 Feb 2006 2:59] Tom Wilson 
(giving more detail: version and OS)
[8 Feb 2006 3:01] Tom Wilson 
apparently this has been reported and fixed. Thanks guys. :-)