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

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

my code is like this:

        using (var contxt = new realtydbEntities())
        {
            var status = GetStatus();

            var repIssue = new RepairIssue()
            {
                CreaterId = AuthorId,
                RepairItemDesc = this.txtDescription.Text,
                CreateDate = DateTime.Now,//here's the problem
                RepairIssueStatu = status
            };

            contxt.AddObject("RepairIssues", repIssue);
            contxt.SaveChanges();
        }

the CreateDate property mapping to a column which type is smalldatetime.

how to make this code run?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 🙂

Method 2

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)

Method 3

Add this to your model class:

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

Method 4

SqlDateTime will allow you to do what you need.

Method 5

I got this error because I had added a datetime column to my SQL table and application WITHOUT removing the old data. I found that I could update new records; but, the records that were in the table prior to the added field threw this error when an update was attempted on one of them.

Method 6

check your migration content. I changed my model like this”

public DateTime? CreationDate { get; set; }

then add new migration and finally run update database

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0