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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect to SQL 2008 r2 server over the Internet 4 72
SQL syntax question 6 68
Tracking Problematic Page Splits 1 44
Http Post send by Store Procedure Help 5 20
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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.…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

736 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