MySQL and the .NET languages handle date and time information differently, with MySQL allowing dates that cannot be represented by a .NET data type, such as '0000-00-00 00:00:00'. These differences can cause problems if not properly handled.

In this section we will demonstrate how to properly handle date and time information when using Connector/NET.

The differences in date handling can cause problems for developers who use invalid dates. Invalid MySQL dates cannot be loaded into native .NET DateTime objects, including NULL dates.

Because of this issue, .NET DataSet objects cannot be populated by the Fill method of the MySqlDataAdapter class as invalid dates will cause a System.ArgumentOutOfRangeException exception to occur.

The best solution to the date problem is to restrict users from entering invalid dates. This can be done on either the client or the server side.

Restricting invalid dates on the client side is as simple as always using the .NET DateTime class to handle dates. The DateTime class will only allow valid dates, ensuring that the values in your database are also valid. The disadvantage of this is that it is not useful in a mixed environment where .NET and non .NET code are used to manipulate the database, as each application must perform its own date validation.

Users of MySQL 5.0.2 and higher can use the new traditional SQL mode to restrict invalid date values. For information on using the traditional SQL mode, see Sección 5.3.2, “El modo SQL del servidor”.

Although it is strongly recommended that you avoid the use of invalid dates within your .NET application, it is possible to use invalid dates by means of the MySqlDateTime datatype.

The MySqlDateTime datatype supports the same date values that are supported by the MySQL server. The default behavior of Connector/NET is to return a .NET DateTime object for valid date values, and return an error for invalid dates. This default can be modified to cause Connector/NET to return MySqlDateTime objects for invalid dates.

To instruct Connector/NET to return a MySqlDateTime object for invalid dates, add the following line to your connection string:

  Allow Zero Datetime=True
  

Please note that the use of the MySqlDateTime class can still be problematic. The following are some known issues:

Because of the known issues, the best recommendation is still to use only valid dates in your application.

The .NET DateTime datatype cannot handle NULL values. As such, when assigning values from a query to a DateTime variable, you must first check whether the value is in fact NULL.

When using a MySqlDataReader, use the .IsDBNull method to check whether a value is NULL before making the assignment:

Visual Basic Example

If Not myReader.IsDBNull(myReader.GetOrdinal("mytime")) Then
    myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"))
Else
    myTime = DateTime.MinValue
End If
  

C# Example

if (! myReader.IsDBNull(myReader.GetOrdinal("mytime")))
    myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"));
else
    myTime = DateTime.MinValue;
  

NULL values will work in a dataset and can be bound to form controls without special handling.