asp.net mvc - An overflow occurred while converting to datetime using EF4

Question

I have a windows application working with a SQL Compact 4.0 database, using EF 4.1 and code-first approach. I cannot save an object to the database since I'm getting an exception, with inner exception "An overflow occurred while converting to datetime" when trying to save the type Quotation:

public class Quotation
{
    public int ID { get; set; }

    public string Name { get; set; }

    public DateTime DateCreated { get; set; }

    public ContactPerson ContactPersonAssigned { get; set; }

    public string OurReference { get; set; }

    public string QuotationDataString { get; set; }
}

I read that this error can be caused by a mismatch between my application settings and the sql compact database settings regarding the conversion of a date. I'm not sure about it, since my sdf database file has a field which is correctly named "DateCreated", not-nullable and of type "datetime".

I'm new to SQL compact. Could you help me debug this problem?

Answer 1

If your model has non-nullable property of type DateTime, when you post a form with empty value for that property, it is automatically set to DateTime.MinValue, which is in .net 01/01/0001 (DateTime.MinValue on MSDN)

(As a side note, you can change this behavior by implementing your own IModelBinder for DateTime which could i.e. throw a validation exception if attempted value is null/empty and property is not nullable).

If you try to save that value (DateTime.MinValue) into database, you will get conversion error if database field is of sql type datetime, because .net DateTime.MinValue is less than SQL datetime minvalue (01/01/1753) and therefore cannot be converted to sql value. (SQL datetime min value on MSDN)

This error will not occur on newer versions of MS SQL Server, which have datetime2 datatype which allows values from 01/01/0001 to 31/12/9999 (SQL datetime2 on MSDN) (if datetime2 is used for that field, of course).