[SOLVED] BLToolKit: how to fetch 'empty' datetime field?

BLToolKit: how to fetch 'empty' datetime field?

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

Solution

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"/>