datetime2 and ProviderManifestToken in Entity Framework

Question

I have an MVC app using Entity Framework and a SQL 2008 DB. I used the EF wizard to generate my data model.

I have a SQL table with a standard SQL DateTime column. The EF model is using System.DateTime.

But when I try to insert a new record into this table from my application, without specifying a value for this DateTime column, I get the error:

"The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated."

I Googled a bit and found that people are editing the edmx and changing the ProviderManifestToken. Ovbiously that will be overwritten, so is there a more permanent fix or way to fix this?

Answer 1

The problem is because, like you said, you're not setting any value for that DateTime property on the entity which causes it to default to '0001-01-01' which is definitely out of range for datetime column on SQL Server. The datetime type date range is January 1, 1753, through December 31, 9999.

To solve this, you have to either assign an in range value to that property before sending it to SQL Server, or change your DB column type to datetime2 which has a date range of 0001-01-01 through 9999-12-31.