Skip to main content

Entity Framework, Sql Server 2008 why you should use DateTime2

Image
By

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.

Labels:

Comments

  1. Anonymous30 March 2010 at 07:11

    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.

    ReplyDelete
  2. Anonymous30 March 2010 at 09:33

    I changed my datetime columns to datetime2 but I still get the error is there something else I should do?

    ReplyDelete
  3. Will Beattie30 March 2010 at 11:33

    You 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.

    The 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.

    ReplyDelete
  4. Anonymous30 March 2010 at 22:59

    Hi Again,
    Very 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?!

    ReplyDelete
  5. Florin daneliuc27 September 2011 at 16:21

    wrong. If you insert '0001-01-01' then there is something wrong with your code and structure. Because:
     - 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.

    ReplyDelete
  6. Will Beattie27 September 2011 at 17:17

    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

Post a Comment

Popular posts from this blog

Freeing Disk Space on C:\ Windows Server 2008

By Will Beattie
  I just spent the last little while trying to clear space on our servers in order to install .NET 4.5 . Decided to post so my future self can find the information when I next have to do this. I performed all the usual tasks: Deleting any files/folders from C:\windows\temp and C:\Users\%UserName%\AppData\Local\Temp Delete all EventViewer logs Save to another Disk if you want to keep them Remove any unused programs, e.g. Firefox Remove anything in C:\inetpub\logs Remove any file/folders C:\Windows\System32\LogFiles Remove any file/folders from C:\Users\%UserName%\Downloads Remove any file/folders able to be removed from C:\Users\%UserName%\Desktop Remove any file/folders able to be removed from C:\Users\%UserName%\My Documents Stop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistribution Deleting an Event Logs Run COMPCLN.exe Move the Virtual Memory file to another disk However this wasn’t enough & I found the most space was
501 comments
Read more

CPF Contribution Rates for new Singapore Permanent Residents (SPR’s)

By Will Beattie
Recently my wife and I applied and got approved for Singapore Permanent Residency. After completing the formalities the most significant immediate change is the contribution to CPF which is Singapore’s mandatory social security savings scheme requiring contributions from employers and employees. CPF contributions start from the date you obtain SPR status, which is the date of the entry permit.   Being a relentless budgeter I needed to know exactly how much I and my employer would have to contribute so that I could adjust my budget accordingly as the employee contributions get deducted from the monthly salary. After doing some research I discovered that there is a “graduated” approach to CPF contributions for new SPR’s where the contributions gradually increase in the first and second year and then upon reaching the third year are at the full amount. Note: There is an option for employers to contribute the full amount for year 1 and year 2 and the employee can use the graduated ra
340 comments
Read more

Populating Duplicate Fields with DocuSign's REST API

By Will Beattie
If you're using DocuSign's REST API for integrating e-Signing into your application then it's possible you'll come up against the issue of duplicate fields not populating. This is when you have the same field with the same label e.g. Company Name in multiple places on the Document but you only want to send a single label, value instance to the API and have it populate in all places where the field is. When you pass the label and value like so: { label: "company_name", value: "Blogger.com" } If you have the field company_name more than once in the document then only the first field will be populated. After a lot of digging into the DocuSign documentation I discovered the solution is to append "\\*" to the label name: { label: "\\*company_name", value: "Blogger.com" } Why this isn't the default behaviour is beyond me but the solution works as expected. DocuSign Rest API Documentation
107 comments
Read more