If you’re using Sql Server 2008 with Entity Framework and have a DateTime field you have no doubt come across this error when Inserting or Updating an Entity
System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The problem here lies in the fact that the .NET DateTime.MinValue equals 0001-1-1 but the Sql Server DateTime only covers 1753-1-1 through 9999-12-31
The very simple solution is to use DateTime2 in Sql Server 2008 which covers 0001-1-1 through 9999-12-31.
DateTime2 has also has some other benefits.
Thank you for pointing to this problem.I made some working pages for some tables and the error didn't appear.But when I deleted one of the tables and make a new relation in its place the error appeared.In both cases I was using SQL 2008 with Entity Framework but I think we should find out what exactly cause the error which unfortunatily I still don't know it and hope some one got the exact answer.
ReplyDeleteI changed my datetime columns to datetime2 but I still get the error is there something else I should do?
ReplyDeleteYou need to check if any of your Date property is being set in code to a date outside of the range of accepted values 0001-1-1 through 9999-12-31.
ReplyDeleteThe best approach would be to use SQL Server Profiler and see what the raw SQL query looks like, this will tell you what property/field is causing the exception.
Hi Again,
ReplyDeleteVery interesting...
I have another table who I can insert to without any problems(Using Entity Framework).The date fields in that table are DateTime but even so using the SQL Profiler (as you told me) I see something like @2 datetime2(7)
What does this mean? That table fields are DateTime I didn't change them so why I'm seeing @2 datetime2(7) I don't understand?!
wrong. If you insert '0001-01-01' then there is something wrong with your code and structure. Because:
ReplyDelete- that should mean that there is no date and then you should have a null
- somewhere in code you forgot to set that field.
Fix the cause not the effect.
Agreed that you should use a nullable date if that is the intent but you're assuming '0001-01-01' would never be a valid date value by the application. If you want to allow date values from 0001-1-1 through 9999-12-31 then you need to use DateTime2.
ReplyDelete