DATETIME parameters getting DATETIME2 values from .NET - by mrdenny

Status : 


Sign in
to vote
ID 519863 Comments
Status Active Workarounds
Type Bug Repros 3
Opened 12/11/2009 11:07:30 AM
Access Restriction Public


When creating a new stored procedure on SQL 2008 and using the older DATETIME datatype something is converting the value into a DATETIME2 style value when it tries to put it into the DATETIME datatype.
Sign in to post a comment.
Posted by Laurence Davis on 10/14/2010 at 4:22 AM
Hi, I'm using SQL 2008 R2 and still have this problem. It's very inconvenient when tracing RPCs to replay against a test database as I receive the error:
"Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string."
The only way around it (other than some clever scripting) appears to be to edit it manually.
A previous post said that it would be fixed in the "next major release" but it hasn't been fixed in R2.
Posted by Microsoft on 12/14/2009 at 12:10 PM

This is a problem with RPC trace, which results in displaying extra zeros in SQL Server Profiler because we always print 7 fractional digit for the type DBTYPE_DBTIMESTAMP regardless of whether this comes from datetime, smalldatetime, datetime2, or datetimeoffset.

Rest assured, the stored procedure taking a datetime parameter, and being sent a .NET DateTime is actually not using a datetime2 type despite what SQL Server Profiler shows. The stored procedure is receiving a datetime. No conversion is happening on the server in this case. This is only a tracing problem.

Thank you for reporting that issue, we had discovered and fixed it a few month ago, and the next major release of SQL Server will not have that behavior. We will display a scale of 3 for datetime, 0 for smalldatetime, x for time(x), datetime2(x) and datetimeoffset(x).

Miles Trochesset - SQL Server engine team
Posted by mrdenny on 12/11/2009 at 11:09 AM
People have been talking about this problem here for a bit now, but no one could actually confirm that it was submitted to the PG for resolution.