[+/-]
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:
Data binding for invalid dates can still cause errors (zero dates like 0000-00-00 do not seem to have this problem).
The ToString
method return a date
formatted in the standard MySQL format (for example,
2005-02-23 08:50:25
). This differs from
the ToString
behavior of the .NET
DateTime class.
The 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.
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.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.