Sunday, April 17, 2011

MySqlConversionException when accessing DateTime field from DataReader

Hello again,

I have a C# application over MySql, using MySQL Connector; I'm trying to make a DataReader request, the query executes fine, however, when trying to access a DateTime field, i'm getting MySqlConversionException {"Unable to convert MySQL date/time value to System.DateTime"}

this is the prototype

if (dr != null && !dr.Read()) return;

sesion.Id = Convert.ToInt32(dr["id"]);
sesion.Usuario = Convert.ToInt32(dr["usuario"]);
sesion.Estado = Convert.ToByte(dr["estado"]);
// doesn't work
sesion.FchCreacion = Convert.ToDateTime(dr["fch_creacion"]);

Any suggestions? Thanks in advance

From stackoverflow
  • I'd suggest it may be a culture specific error - is the applicaiton on the same server as the DB, and do they have the same culture settings?

    Also, is the column definitely a datetime in MySQL?

  • It could also be a DBNull value.

  • This error sometimes occurs if you have zero datetime values in your MySQL database (00/00/0000 00:00). Try adding this to the end of your connection string:

    Allow Zero Datetime=true
    Jhonny D. Cano -Leftware- : Thanks, i didn't spot those values, nice trick
  • There are a few potential gotchas when converting between MySQL dates/times and .NET DateTimes, but there's a useful section in the MySQL documentation with advice on how to handle the issues.


Post a Comment