SqlParameter Datetime SqlValue difference with Value

Question

I have a datetime value to pass into a SqlParameter.

DateTime object value

However when pass to myCmd.Parameters.Add("@TrxDate", adt_TrxDate);

SqlParameter SqlValue & Value property is difference value

Found that it add a day when executing query. I found this issue in profiler and finally find out SqlParameter SqlValue & Value property is different value. Why it is happen and any idea?

Answer 1

Root cause is, .NET DateTime has higher precision then SQL Server's DateTime. So it is rounded off. SQL Server 2008 on wards, DateTime2 supports higher precision.

Since data type is DateTime in SQL, SQL Parameter is rounding .net DateTime to nearest Sql DateTime. Since it is rounding algorithm, it may add .003,.007 or remove, you can look at this SO question for more details. Undesired rounding of DateTime in SQL Server , so sometimes, if added micro seconds adds up, it may actually change to next day if it was on boundary between two days.

From MSDN,

https://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx

DateTime. Date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds.

DateTime2 Date and time data. Date value range is from January 1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds.

So I guess if you change Parameter Type to DateTime2 it would preserve actual time. However, unless your SQL Server has column's type as DateTime2, it this will have no effect.

Answer 2

You have to use like the following by specifying the SqlDbType for the parameter :

   myCmd.Parameters.Add("@TrxDate",SqlDbType.Date).Value =adt_TrxDate;

use SqlDbType.DateTime if it is DateTime

or you can use AddWithValue

  myCmd.Parameters.AddWithValue("@TrxDate",adt_TrxDate);

You can refer this thread for the difference between these two