2

I've just deployed server code that used to work literally a week ago. The part of the code that results in the error did not change, however the object itself did.

It is a Model First approach (converted from Database First), and I've added two association fields and two new tables to the model.

The part of the code that throws the error is mission critical. It basically disables concurrent editing of the value by setting a timestamp. However for some reason it does not work.

The particular code:

user.AlertConcurrency = DateTime.UtcNow;
db.SaveChanges();

The error:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

Now, the user.AlertConcurrency field is a datetime type in the database. I don't see why it even tries to do datetime to datetime2 conversion here. What am I missing?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
fonix232
  • 2,132
  • 6
  • 39
  • 69
  • This probably means that there is *another* DateTime property that is null, but not nullable in the database. – Gert Arnold Aug 23 '16 at 17:46
  • Is this failing on data already saved in the DB? Sounds like you have data stored as a `datetime2`, which doesn't have a vaild value for `datetime`. – Charles Aug 23 '16 at 17:46
  • Do the answers to [this question](http://stackoverflow.com/q/1331779/215552) help? – Heretic Monkey Aug 23 '16 at 17:46
  • @GertArnold as I previously have saved the user object, and it contained all the required datetime fields as non null, and I do not modify any of those fields, I doubt this is the case. – fonix232 Aug 23 '16 at 18:07
  • @Charles I do not have any datetime2 fields in the db, only datetimes. – fonix232 Aug 23 '16 at 18:29

1 Answers1

1

datetime2 has a larger range that datetime, so you probably have a null datetime2 which is "0001/01/01" trying to be converted into a datetime which the min value is "1753/01/01" thus the conversion fails. A null C# DateTime property would thus be your datetime2 value thats causing the problem.

Check your model is fully updated/in sync with your database and check for null values in your DateTime properties. If you have null database values or want to save null DateTime then consider using the nullable DateTime in your model and database.

Wurd
  • 465
  • 2
  • 15
  • I'm not using any datetime2 in the database - I specifically checked for it, and ALL fields are datetime. I do have some null datetimes, but they are nullable by default. – fonix232 Aug 23 '16 at 18:28
  • Remember datetime is the SMALLER date range. Your c# DateTime null value is a LARGE datetime2 "0001/01/01" so when SQL tries to save it to your SMALLER datetime, it is out of range. datetime can only reach "1753/01/01". – Wurd Aug 23 '16 at 18:32
  • Is there any preferred way to make a Model First generated database use datetime2 instead of datetime? – fonix232 Aug 23 '16 at 19:50
  • 1
    `modelBuilder.Properties() .Configure(c => c.HasColumnType("datetime2"));` – Wurd Aug 23 '16 at 20:02