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 '
00:00:00'. These differences can cause problems if not
The following sections demonstrate how to properly handle date and time information when using Connector/Net.
Connector/Net 6.5 and higher support the fractional seconds feature introduced in MySQL 5.6.4. Fractional seconds could always be specified in a date literal or passed back and forth as parameters and return values, but the fractional part was always stripped off when stored in a table column. In MySQL 5.6.4 and higher, the fractional part is now preserved in data stored and retrieved through SQL. For fractional second handling in MySQL 5.6.4 and higher, see Fractional Seconds in Time Values. For the behavior of fractional seconds prior to MySQL 5.6.4, see Section 11.3.6, “Fractional Seconds in Time Values”.
To use the more precise date and time types, specify a value
from 1 to 6 when creating the table column, for example
representing the number of digits of precision after the decimal
point. Specifying a precision of 0 leaves the fractional part
out entirely. In your C# or Visual Basic code, refer to the
Millisecond member to retrieve the fractional
second value from the
returned by the
DateTime object returned by the
GetDateTime function also contains the
fractional value, but only the first 3 digits.
For related code examples, see the following blog post: https://blogs.oracle.com/MySqlOnWindows/entry/milliseconds_value_support_on_datetime
The differences in date handling can cause problems for
developers who use invalid dates. Invalid MySQL dates cannot be
loaded into native .NET
Because of this issue, .NET
cannot be populated by the
Fill method of the
MySqlDataAdapter class as invalid dates will
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
Section 5.1.7, “Server SQL Modes”.
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 data type.
MySqlDateTime data type 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
class can still be problematic. The following are some known
Data binding for invalid dates can still cause errors (zero dates like 0000-00-00 do not seem to have this problem).
ToString method return a date
formatted in the standard MySQL format (for example,
2005-02-23 08:50:25). This differs from
ToString behavior of the .NET
MySqlDateTime class supports NULL
dates, while the .NET DateTime class does not. This can
cause errors when trying to convert a MySQLDateTime to a
DateTime if you do not check for NULL first.
Because of the known issues, the best recommendation is still to use only valid dates in your application.
DateTime data type 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
When using a
MySqlDataReader, use the
.IsDBNull method to check whether a value is
NULL before making the assignment:
If Not myReader.IsDBNull(myReader.GetOrdinal("mytime")) Then myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime")) Else myTime = DateTime.MinValue End If
if (! myReader.IsDBNull(myReader.GetOrdinal("mytime"))) myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime")); else myTime = DateTime.MinValue;
NULL values will work in a data set and can
be bound to form controls without special handling.