Conversion of a datetime2 error using a nullable date column and EF 6.1.3

Question

We are using a database first approach (SQL Server) and defined a nullable date column. We generated the context in Visual Studio en confirmed that [Column(TypeName = "Date")] was added. When we save a new record (we confirmed that the value is either null or has a value > 01-01-2000) we got following exception.

Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value" exception.

We looked at other SO questions and this article but we could not find the real reason for this error other than changing the database column to a nullable datetime2. Is this a known restriction of EF?

Possible duplicate of [Conversion of a datetime2 data type to a datetime data type results out-of-range value](http://stackoverflow.com/questions/1331779/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-results-out-of-range) — Bhavik Patel, Jun 02 '16 at 04:39
We tried to reproduce the described problem on another computer and could not reproduce it. Both the Code First and the Database First approach worked perfectly. — Jeroen Heier, Jun 03 '16 at 06:16

score 1 · Accepted Answer · edited Jun 02 '16 at 05:49

This usually happens when the datetime field in database is optional and value is not set. its one of the entity-framework architectural things.

I would recommend one to debug the issue, see if it shows the date like this 0001/1/1 and in that case initializing the field with some value or the property nullable as DateTime is a value-type struct

as you said (we confirmed that the value is either null or between DateTime.MinValue and DateTime.MaxValue) then the only solution one can find is this:

var value = values[name];
if (value is DateTime)
{
    var date = (DateTime)value;
    if (date < SqlDateTime.MinValue.Value)
    {
        values[name] = SqlDateTime.MinValue.Value;
    }
    else if (date > SqlDateTime.MaxValue.Value)
    {
        values[name] = SqlDateTime.MaxValue.Value;
    }
}

NOTE SqlDateTime.MinValue != DateTime.MinValue

In entity framework set the nullable property in the edmx file to True

Hope this helps you in any way :)

Conversion of a datetime2 error using a nullable date column and EF 6.1.3

1 Answers1