Home Dashboard Directory Help

DATETIME parameters getting DATETIME2 values from .NET by mrdenny



Sign in
to vote
Type: Bug
ID: 519863
Opened: 12/11/2009 11:07:30 AM
Access Restriction: Public
User(s) can reproduce this bug


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 http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/6d455ce9-d699-46b0-9b1f-04cc8a3279ea for a bit now, but no one could actually confirm that it was submitted to the PG for resolution.
Sign in to post a workaround.
Posted by mrdenny on 12/11/2009 at 11:08 AM
The only workaround that I have found is to pass the datetime value in as a character string by telling .NET that the value is a varchar instead of a datetime. At this point this workaround is all over our code base and we would really like to clean it up.