BLToolKit: how to fetch 'empty' datetime field?

Question

Request Execute is failed if one of fields to be mapped has DateTime field and corresponding value in DB has '0000-00-00' or '0001-01-01'. The following error is returned

Unable to convert MySQL date/time value to System.DateTime

Is there any possibility to fetch such value?

I've tried to specify the 'DateTime?' value as property type - it doesn't help too (actually, I didn't expect that to be helpful).

P.S. I use MySql 5.1

Answer 1

I came across a similar problem using NHibernate with the same error in an exception.

It's due to MySQL's unique "feature" of allowing invalid dates in a DATE field, especially using 0000-00-00 as a default value for DATE NOT NULL columns. When such a date is encountered, it throws an exception when converting itself to a DateTime.

The suggested solution for this was to add

Allow Zero Datetime=True;

to the connection string, however in practice this did not work for me. I eventually solved the problem by altering the connection string adding

Convert Zero DateTime=true;

so your app.config section would look something like this

  <connectionStrings>
<add
  name="ConnectionString.MySql"
  connectionString="Server=localhost;Port=3306;Database=BLT;Uid=someuser;Convert Zero DateTime=true;"
  providerName="MySql.Data.MySqlClient"/>

Answer 2

Have you tried the MapValue attribute? I'm not sure if this will work but...

[MapValue(null, "0000-00-00")]
[MapValue(null, "0001-01-01")]
public DateTime? theDate;

Answer 3

i think you have to control it by another property.

 [MapField("the_date")]
 public DateTime? theDate; // Map
 [MapIgnore]
 public DateTime theDateControl
 {
     set {
         if(theDate.HasValue)
         {
            ....
         }
     }
 }