4

My Code First models have System.DateTime properties. When the database seeding code is invoked, it throws this exception:

SqlException (0x80131904): The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

I am creating a new DateTime object with the constructor. Code First is creating the database schema. How do I overcome this apparent bug in Code First? I am not particular about my datatype. I merely need to store a date, and optionally a time of day, but not crucial.

I have searched and read a lot of posts, but none with this error specify they come from CodeFirst-generated data scheme. The closest answer I found involved making a change to the ProviderManifestToken in the edmx file, but my project does not have an edmx file. I did not use the designer, I am using Code First.

Rui Jarimba
  • 11,166
  • 11
  • 56
  • 86
UniqueMan
  • 56
  • 1
  • 5

2 Answers2

3

EF maps DateTime in .NET to datetime in SQL Server. The types have a different range: datetime can store only dates later than around the year 1750. If you try to store a DateTime in your entity with an earlier date or an unitialized DateTime (which has year 1) you get the exception because SQL Server cannot store this.

Solution:

  • Either make sure that the dates you want to store are later than 1750
  • Or map you DateTime properties explitely to datetime2 in SQL Server which has a wider range. Example how to define this mapping with Fluent API: https://stackoverflow.com/a/8044310/270591

Actually I also had expected a default mapping to datetime2 because it fits better to DateTime in .NET. But for some reason they decided to use datetime as default.

Community
  • 1
  • 1
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • Thanks please see my own answer for an explanation. – UniqueMan Feb 11 '12 at 00:02
  • @billy: You can use annotations to define the `datetime2` type: `[Column(TypeName = "datetime2")]`. There is no attribute to define the precision explicitly. But you could try (example for precision 0): `[Column(TypeName = "datetime2(0)")]`. I am not sure if that will work though. – Slauma Jul 10 '12 at 10:33
  • @billy: The second annotation with `[Column(TypeName = "datetime2(0)")]` doesn't seem to work. I just tested it and I get an exception. It looks that the precision can only be defined with Fluent API. – Slauma Jul 10 '12 at 10:48
0

Sorry I found out after more careful debugging that the error was coming from a data entity initialization that involved me not specifying any value for the DateTime.

I had thought it was coming from a long data seeding statement that involved new DateTime(2012, 2, 19, 19, 0, 0) or some such. Thanks to Slauma for his efforts - he was correct that it was due to unitialized values.

Rui Jarimba
  • 11,166
  • 11
  • 56
  • 86
UniqueMan
  • 56
  • 1
  • 5