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

Posted on 2008-10-27
Medium Priority
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.
Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

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 1500 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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