MySqlConversionException when accessing DateTime field from DataReader

Question

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

Answer 1

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?

Answer 2

It could also be a DBNull value.

Answer 3

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

Answer 4

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.