CODEWITHSUNDEEP
c#asp.netentity-frameworkwebforms
Scott 混合理论
Scott 混合理论

Reputation: 2332

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?

Upvotes: 25

Views: 55649

Answers (6)

Ghadir Farzaneh
Ghadir Farzaneh

Reputation: 458

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

public DateTime? CreationDate { get; set; }

then add new migration and finally run update database

Upvotes: 0

Howie Krauth
Howie Krauth

Reputation: 51

Add this to your model class:

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

Upvotes: 4

Jason
Jason

Reputation: 4322

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)

Upvotes: 13

Milton
Milton

Reputation: 968

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 :)

Upvotes: 25

midohioboarder
midohioboarder

Reputation: 480

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.

Upvotes: 0

Rob
Rob

Reputation: 1081

SqlDateTime will allow you to do what you need.

Upvotes: 1

Related Questions