33

I have a table w/ a sql date data type. When I look at the EDM markup, the storage element reflects this. The conceptual entity has a data type of DateTime (there doesn't appear to be a Date data type). When I call save changes and have a DateTime instance associated w/ the entity I want to persist, I get the following 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.

How can I use the date data type in my schema and have EF behave? Thanks!

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
James Alexander
  • 6,132
  • 10
  • 42
  • 56

1 Answers1

49

Sounds like your SQL date column is non-nullable, but you haven't initialized the date on your entity before saving?

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • 1
    Yeah I just realized this had nothing to do w/ my date column, I have another DateTime column that has a default specified within the db but I forgot to change the the StoreGeneratedPattern property in the SSDL to get this working. My bad. – James Alexander Dec 16 '09 at 20:03
  • 1
    @Pop Catalin: Hard to blame the EF here; it's behaving correctly. SQL Server just has a really weirdly limited `DateTime` range. – Craig Stuntz Oct 08 '10 at 15:06
  • I meant in general, this was just one of the many issues I've encountered in a very short time period ... (And BTW, EF came after SQL Server, I've expected more from EF) – Pop Catalin Oct 11 '10 at 12:03
  • 1
    Well, I was having this same problem tonight, and magically somehow I found my way to this page as a top result to my google search and thanks to this answer, realized i just needed to make my domain (POCO) model (via EF code-first) Nullable instead of DateTime and now things work! Yay! – Funka Jan 26 '13 at 03:00
  • The valid range for SQL Server's DATETIME type is January 1, 1753, through December 31, 9999. If EF detects that the value is outside this range, it automatically tries to use DATETIME2 instead of DATETIME to store the value. To avoid this unwanted behavior, you have to add code in your application to make certain the value is within the valid DATETIME range. Also see http://stackoverflow.com/questions/3586566/datetime2-error-when-using-entity-framework-in-vs-2010-net-4-0#3586607. – Suncat2000 Mar 24 '16 at 12:39