4

I have defined a Nullable DateTimeproperty in one of my classes:

public DateTime? ControlDate { get; set; }

When using EF 6 CodeFirst to generate the database (SQL Server 2008) from my model I get:

ControlDate null

The problem is that when I save my instance with ControlDate=null I get this exception:

conversion of a datetime2 data type to a datetime data type
resulted in an out-of-range value

I have read multiple related posts and articles saying that this usually happens when you define a Non-nullable DateTime property and try to save it without setting a valid date before, and some people suggest setting the property as nullable in case the property value can be null (which is my particular case).

My question is: why is EF trying to set a default date when my property and column type are nullable. Null should be a valid value and should flow all the way to the database without any other conversion in between.

Here a related article: Conversion of a datetime2 data type to a datetime data type results out-of-range value

EDIT: : Here is a very similar question. very detailed explanation, in case anyone is interested.

Lessons Learned: Just wanted to clarify that after looking closer, I figured out it was an issue on my side. Right before saving my object it was being set to: 

myObject.ControlDate = new DateTime()

Which, while inspected, it displayed the default incompatible date 1/1/0001. Which is well known to cause this exception. So my conclusions:

  • A model declaring a non-nullable DateTime property will result in a datetimedata type in SQL Server.
  • An instance of a class declaring a nullable DateTimeproperty will be able to save to the DB as null
  • It is very important to set a valid default date manually (in case <> NULL), otherwise it will set it to 1/1/0001 and throw the exception
Community
  • 1
  • 1
Adolfo Perez
  • 2,834
  • 4
  • 41
  • 61
  • Are you sure it's not because another `DateTime` property? – tia Sep 02 '14 at 12:37
  • change your column type to datetime2(7) – gilles emmanuel Sep 02 '14 at 12:42
  • Please read this once [DateTime2](http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server) – Pankaj Nema Sep 02 '14 at 12:46
  • When you inspect the content of your object before your call to `SaveChanges`, does the `ControlDate` property equal `null` or does it equal 1/1/0001? – Rob Epstein Sep 02 '14 at 12:47
  • @RobEpstein Yes when I inspect it my `ControlDate` property has a NULL value – Adolfo Perez Sep 02 '14 at 13:06
  • @tia There are only other 2 Non-Nullable properties in my object with correct `DateTime` values. – Adolfo Perez Sep 02 '14 at 13:07
  • Changing my `ControlDate`property to `datetime2`seems to work fine @gillesemmanuel – Adolfo Perez Sep 02 '14 at 13:20
  • Entity Framework is the problem. A CLR `DateTime` value maps to SQL `datetime` if the timestamp is within a normal range of values ("modern" dates from 01/01/1753 through 12/31/9999). If it's outside the normal range, as is 01/01/0001, the value is mapped to SQL `datetime2` (01/01/0001 through 12/31/9999). EF leaves it up to _you_ to make sure your value ends up using the correct SQL datatype. – Suncat2000 Dec 27 '19 at 13:06

1 Answers1

5

As far as I'm aware, it's actually the SQL Server datetime2 type that maps directly to .NET's DateTime type, so you may need to change the type of your column in SQL Server

Joe
  • 1,214
  • 3
  • 15
  • 33
  • Yes, that seemed to work @Joeb454. Now I'm thinking whether I need to change all my `DateTime` properties to `datetime2` – Adolfo Perez Sep 02 '14 at 13:28
  • That depends on whether you're mapping them directly through to SQL Server, or whether you want to handle mapping in each save. Personally, given the option, I'd change the SQL Server data types, but that then depends on your situation; it may not be practical to make such a change, or may not be possible (e.g. In a more corporate environment) – Joe Sep 02 '14 at 13:32
  • I just upvoted this because you saved me. Wish i saw this 2 hours ago! – WorkJ Feb 07 '22 at 19:32