How to fix "SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."

I had the same exception, but it was because a non nullable datetime property that taking the min datetime value. That wasn't a smalldatetime at DB, but the min datetime of C# exceed the limit of min datetime of SQL. The solution was obvious, set the datetime properly. BTW, the code wasn't mine, and that's why I wasn't aware of that property :)


The root of your problem is that the C# DateTime object is "bigger" than SQL's smalldatetime type. Here's a good overview of the differences: http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

So really your options are:

  1. Change the column type from smalldatetime to datetime (or datetime2)
  2. Instead of using EF, construct your own SQL Command (and you can use SqlDateTime)

Add this to your model class:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));
    }