Sun 6 Aug 2006
MySQlDateTime Invalid Dates in Connector/NET
Posted by NZEYIMANA Emery Fabrice under C# , MySQL , Software , Visual Basic , Visual Studio IDE[2] Comments
In some installations of the MySQL server, some invalid dates are legal.
At the URL http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html I can read:
“MySQL version through 4.1 accept certain “illegal” values for dates, such as '1999-11-31'. This is useful when you want to store a possibly incorrect value specified by a user (for example, in a web form) in the database for future processing. MySQL verifies only that the month is in the range from 0 to 12 and that the day is in the range from 0 to 31. These ranges are defined to include zero because MySQL allows you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is extremely useful for applications that need to store a birthdate for which you do not know the exact date. In this case, you simply store the date as '1999-00-00' or '1999-01-00'. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD that require complete dates.
MySQL also allows you to store '0000-00-00' as a “dummy date.” This is in some cases more convenient, and uses less data and index space, than storing NULL values.”
This behaviour causes problems when using the MySQL Connector/NET. First, Visual Studio 2005 DateTime structure does not accept such invalid date values. It only accepts values between “0001-01-01 00:00:00″ through “9999-12-31 23:59:59.99999″ and these values should be real dates (Check remarks at system.datetime ).
To deal with this problem, connector/NET has MySqlDateTime type that can contain such invalid dates. This class has a property, IsValidDateTime, that is set to true when the date is valid and false otherwise. Currently, the way this property is calculated does not guarantee that the value contained in a MySqlDateTime object can be easily converted into a legal DateTime object. Currently, this property will return FALSE if and only if the object contains a ZERO date (or ZERO dateTime: 0000-00-00).
To overcome this, the property checking should also check if this date is legal in .NET
I have created a sample function that can replace the one in Connector/Net 1.0.7 and I named it IsStrictlyValidDateTime.
Its definition looks like:
[code lang="C#"]
///
/// Indicates if this object contains a value that can be represented as a DateTime
///
public bool IsStrictlyValidDateTime
{
get
{
// In case this represents a zero date, return FALSE
if (year == 0 || month == 0 || day == 0)
{
return false;
}
// Check http://msdn2.microsoft.com/en-us/library/xcfzdy4x(d=ide).aspx
// Here I try assign the value of this object to a DateTime object.
// In case the date is illegal, an Exception will be thrown
// In case the date is legal, the value will be accepted
try
{
DateTime dt = new DateTime(year, month, day);
return true;
} catch (ArgumentOutOfRangeException)
{
//year is less than 1 or greater than 9999.
//-or-
//month is less than 1 or greater than 12.
//-or-
//day is less than 1 or greater than the number of days in month
return false;
} catch (ArgumentException)
{
// The specified parameters evaluate to less than MinValue or more than MaxValue
return false;
}
}
}
[/code]
And the changed file can be seen at /Types/MySqlDateTime.cs
I still don’t understand why a date like “0000-10-01″ was made invalid in .NET (ten months after date ZERO).
