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

Question

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?

Possible duplicate of [The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range?](https://stackoverflow.com/questions/7386360/the-conversion-of-a-datetime2-data-type-to-a-datetime-data-type-resulted-in-an-o) — Tot Zam, Aug 18 '17 at 14:58

Milton · Answer 1 · 2018-02-28T13:00:35.550

22

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

edited Feb 28 '18 at 13:00

answered Oct 07 '13 at 13:56

Milton

928
1
10
22

2

How did you set the datetime properly ? – Hoang Minh Apr 16 '19 at 05:54
The comment in the last phrase is lovely :) – Alex P. May 10 '23 at 13:06

score 11 · Accepted Answer · edited Nov 11 '18 at 21:28

11

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:

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

edited Nov 11 '18 at 21:28

Aaron Bertrand

272,866
37
466
490

answered May 10 '12 at 10:35

Jason

4,232
3
23
31

3

This does not explain why `DateTime.Now will` not fit into a SmallDateTime. It is not so much about the size, but about the range and precision/accuracy that it can store. `DateTime.Now` should always be a valid value for a SmallDateTime field (unless you set your PC clock back 300 years), however I am currently experiencing exactly the same error while trying to simply save a 'DateTime.Now' value of {04/10/2013 12:49:00} into a SQL SmallDateTime field. It is frustrating that I cannot find a correct answer anywhere on SO. – iCollect.it Ltd Oct 04 '13 at 12:00
@TrueBlueAussie An automatic conversion from a "larger" type to a "smaller" type is disallowed because information could be lost in the transformation. This is equally true whether "larger" means "can hold bigger numbers" or whether it means "can hold more precision". C# assumes that those fractional seconds are important, unless you specifically say that they are not (for example, with an explicit cast). – GrandOpener Dec 17 '13 at 20:56
@GrandOpener: Sorry you misunderstood my comment. I simply meant the answer *should have explained why dateTime.Now would not fit into a SmallDateTime*. I was not asking for an explanation :) – iCollect.it Ltd Dec 23 '13 at 14:23

score 3 · Answer 3 · answered Jun 03 '19 at 19:25

3

Add this to your model class:

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

answered Jun 03 '19 at 19:25

Howie Krauth

39
5

score 1 · Answer 4 · answered May 08 '12 at 09:01

1

SqlDateTime will allow you to do what you need.

answered May 08 '12 at 09:01

Rob

1,071
8
10

If you create a `SqlDateTime` from the `DateTime` as you suggest, then use its `Value` for the EF update (as the EF property is a DateTime) you still get exactly the same error (if you have this error). This answer appears incorrect for EF (which the question is about) unless you can clarify how this solves the problem. – iCollect.it Ltd Oct 04 '13 at 12:06

score 0 · Answer 5 · answered Feb 12 '14 at 18:00

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.

score 0 · Answer 6 · answered Nov 10 '19 at 14:37

0

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

public DateTime? CreationDate { get; set; }

then add new migration and finally run update database

answered Nov 10 '19 at 14:37

Ghadir Farzaneh

429
5
6

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

6 Answers6

Linked