SqlDateTimeOverflow exception with nullable DateTime fields with Dapper update

Question

I have a db table with several DateTime fields with null values. These are mapped to nullable DateTimes in my class.

If I try to perform an update with Dapper, within my data layer:

 using (IDbConnection cnn = new SqlConnection(DB.getConString()))
 {
    cnn.Open();
    return cnn.Execute((this.OptionID == 0 ? _insertSQL : _updateSQL), this);              
 }

I get a SqlDateTimeOverflow exception (because the DateTime field is '01/01/0001 00:00:00' rather than null.

Is the only way around this to specify each parameter individually and switch the value to null like this:

 using (IDbConnection cnn = new SqlConnection(DB.getConString()))
 {
    cnn.Open();
    return cnn.Execute("UPDATE MyTable SET MyDateField = @MyDateField", new {MyDateField = (MyDateField.HasValue? MyDateField : Null), etc etc... );

I have about 50 fields in the table so this would be quite a bit of code, plus there is an INSERT method to update similarly too. Is there an easier syntax I am missing?

Answer 1

The issue here is that 01/01/0001 00:00:00 is not a "null value"; if you had used DateTime? I suspect it would have worked fine. However, I also have to recognize that DateTime.MinValue has often (mainly due to .NET 1.1 lacking nullable structs) been used to represent a null value. My preferred suggestion here would be to simply use DateTime?. The min-value map is a bit complicated as we might also consider whether that should be automatically mapped instead to the sql min-value (January 1, 1753).

Re the update statement - maybe add an extension method to map between min-value and null?

public static DateTime? NullIfZero(this DateTime when) {
    return when == DateTime.MinValue ? (DateTime?)null : when;
}

and use:

new { MyDateField = MyDateField.NullIfZero() }

but again, if MyDateField was DateTime?, you could just use:

new { MyDateField }