SQL Server Home
DATETIME parameters getting DATETIME2 values from .NET
12/11/2009 11:07:30 AM
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.
SQL Server 2008 - Enterprise Edition
Windows Server 2008
Operating System Language
Steps to Reproduce
Create a new stored procedure using the DATETIME data type as an input parameter.
Run SQL Profiler to monitor for the stored procedure call.
Use .NET Code similar to:
SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=ServiceEngine;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("dbo.TestGetData", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@FromDate", SqlDbType.DateTime);
DateTime d = DateTime.Now;
p1.Value = d;
Run the .NET code to call the stored procedure.
(Note the Actual vs expected results and the number of characters after the decimal of the parameter value.)
exec dbo.TestGetData @FromDate='2009-07-01 12:45:53.7200000'.
exec dbo.TestGetData @FromDate='2009-07-01 12:45:53.720'.
to post a comment.
Please enter a comment.
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.
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
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.
to post a workaround.
Please enter a workaround.
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.
© 2014 Microsoft