How can I Insert/Update datetime into SQL Server 2005 datetime column in Database from my C# application code?

Posted on 2008-10-27
Last Modified: 2013-11-12
First of all, here is the architecture I am using:
--SQL Server 2005 database (production), 2008 database (development)
--C# (Silverlight) code on client/application side
--Entity Framework (Model that wraps database objects)
--ADO.NET Data Services (connect Entity Framework to Client commands to execute on DB)

In my development database (SQL Server 2008), everything is working fine when I insert a new DateTime into the one of my datetime columns in the DB.  This datetime is generated from the C# client side code (ie. DateTime.Now statement).  My Entity Framework model maps the C# DateTime to the Database datetime type.

However, when I try to run the Insert against my production database (SQL Server 2005), I get an error response that says:  "The version of SQL Server in use does not support datatype 'datetime2'."  But I know that I am not using 'datetime2' anywhere in my C# code or Entity Framework mapping or SQL Server DB's.  So I don't know why it is giving me this error response.  Does anyone have any idea what might be going on here?
Question by:robbersrow
  • 3
  • 3
LVL 39

Expert Comment

ID: 22813826
Can you post the code where you are declaring your datetime datatype in your C# code as well as the procedure code where you are executing it against.  I'm thinking somehow it thinks it's a date data type which is new to SQL 2008 but not in SQL 2005 and lower.

Author Comment

ID: 22813940
COSProxy.COSEntities proxy = new COSProxy.COSEntities(new Uri("COSDataService.svc", UriKind.Relative));

COSProxy.Session s = new COSProxy.Session();

proxy.AddObject("Session", s);

DateTime tempDate = DateTime.Now;

s.StartTime = new DateTime(tempDate.Year, tempDate.Month, tempDate.Day, tempDate.Hour, tempDate.Minute, tempDate.Second, 0);

s.RefreshTime = new DateTime(tempDate.Year, tempDate.Month, tempDate.Day, tempDate.Hour, tempDate.Minute, tempDate.Second, 0);

// Now Save to database.

proxy.BeginSaveChanges(SaveChangesOptions.None, (asyncResult) =>

// Now it returns an error here, saying:  "The version of SQL Server in use does not support datatype 'datetime2'."

// My SQL Server 2005 DB and 2008 DB are both using "datetime" data type for the StartTime and RefreshTime mappings.  In other words, the database "datetime" data type corresponds to a C# "DateTime" object in the way they are mapped in the Entity Framework.  I believe the Entity Framework takes care of any procedure code necessary in the mapping between C# objects and database datatypes.

Author Comment

ID: 22814139
Also, you had mentioned above:  "somehow it thinks it's a date data type which is new to SQL 2008 but not in SQL 2005 and lower."

But the error response I am getting indicates that it my be thinking it's a 'datetime2' data type (not a date data type).  The 'datetime2' data type is also new to SQL Server 2008.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 39

Expert Comment

ID: 22814224
Same thing applies in that it is an unsupported datatype for sql 2005.

Author Comment

ID: 22814826
I realize that datetime2 data type is unsupported for SQL Server 2005.  But nowhere in my code, databases, or Entity Framework do I make any sort of reference to "datetime2" data type.  So I have no idea why this error would be occurring.  All my definitions, mappings, DB columns, etc. are set up to use the basic supported "datetime" data type, that is available in SQL 2005 and 2008.
LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 22815338
I'm not very familiar with .NET but I would imagine that it build your DB references based upon the SQL 2008 instance.  Had you pointed it to 2005 to begin with it would have never thought it could use datetime2.

I think it would be best to get more advice for this from the .NET side.  In order to get some attention to this, since people may not want to jump in now that there are 6 comments, click the request attention link at the top and it will send a question alert to the .NET, SQL Server 2005 and WPF and Silverlight zone subscribers.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Host pages 5 25
C# HTTP GET method sample code 3 41
Near realtime alert if SQL Server services stop. 20 56
MS SQL Inner Join - Multiple Join Parameters 2 23
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now