ADO.NET and SSMS interpret SP params differently or SQL profiler has a bug?

Question

Try to be brief. Here is my table and SP in SQL Server 2008 R2 database:

CREATE TABLE dbo.TEST_TABLE
(
    ID INT NOT NULL IDENTITY (1,1) PRIMARY KEY, 
    VALUE varchar(23)
)
GO

CREATE PROCEDURE USP_TEST_PROCEDURE
( 
    @Param1 varchar(23)
)
AS
BEGIN
    INSERT INTO TEST_TABLE (VALUE) VALUES (@Param1)
END
GO

Here is the C# code:

using (SqlConnection con = GetConection())
{
    using (var cmd = new SqlCommand("USP_TEST_PROCEDURE", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Param1", SqlDbType.DateTime, 23).Value = "2013-12-10 12:34:56.789";
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
}

Pay attention that I intentionally specified the type of @Param1 as DateTime. Now launch SQL Server Profiler and run the .NET code. When done, don’t stop the profiler, just look at the output. I see the following text for the RPC:Completed EventClass:

exec USP_TEST_PROCEDURE @Param1='2013-12-10 12:34:56.790'

Now copy this text, open SSMS, paste to the new query window and run it without any changes. Now stop the profiler and make sure that the SSMS produced the same text in the profiler as ADO.NET app:

exec USP_TEST_PROCEDURE @Param1='2013-12-10 12:34:56.790'

Now select from the TEST_TABLE table. I see the following result:

ID VALUE
1  Dec 10 2013 12:34PM
2  2013-12-10 12:34:56.790

The question is why the two identical commands (from SQL Server Profiler’s point of view) produce different results?

It seems like profiler does not show correctly what is going on behind the scene. After I ran the .NET app I expected to see the following:

DECLARE @Param1 Datetime = '2013-12-10 12:34:56.789';
exec USP_TEST_PROCEDURE @Param1=@Param1

In this case it would be clear why I have 790 milliseconds instead of 789. This is because the datetime type is not very precise. It is rounded to increments of .000, .003, or .007 seconds. See: datetime (Transact-SQL) It would be also clear why I have ‘Dec 10 2013 12:34PM’ instead of ‘2013-12-10 12:34:56.790’. This is because ‘mon dd yyyy hh:miAM (or PM)’ format is used by default when datetime is converted to string. See CAST and CONVERT (Transact-SQL) The following example demonstrates that:

DECLARE @Param1 Datetime = '2013-12-10 12:34:56.789';
DECLARE @Param1_varchar varchar(23);

SELECT @Param1 --2013-12-10 12:34:56.790
SET @Param1_varchar = @Param1;
SELECT @Param1_varchar; --Dec 10 2013 12:34PM
GO

If my understanding is correct, then I would like to rephrase the question: Why SQL Server Profiler shows something strange and confusing? Are there any options to correct this?

Answer 1

Your application is sending a datetime, but since you chose a varchar column, it is implicitly converted on the way in. Just like what happens with print:

DECLARE @d DATETIME = GETDATE();

SELECT @d; -- this is still a datetime

PRINT @d; -- this is implicitly converted to a string

If you don't like this confusing behavior, then stop mixing data types. There is absolutely no good reason to store a datetime value in a varchar column.