MySQL Bugs: #17022: Problems converting "blank" dates (0000-00-00 00:00:00)

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:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

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.

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.

(giving more detail: version and OS)

apparently this has been reported and fixed. Thanks guys. :-)