System.Data.SqlClient.SqlParameter DateTime Bug - by Matthew D. A. Clark

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 767660 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/16/2012 1:57:05 PM
Access Restriction Public


When using a date of 05/05/2009 11:59:59 PM and initializing the variable like this:
StartDateTime = new DateTime(2009, 5, 5, 0, 0, 0, 0);
EndDateTime = StartDateTime.AddDays(1);
EndDateTime = EndDateTime.AddMilliseconds(-1);  

SqlParameter parmEnd = new SqlParameter("@EndDateTime", EndDateTime);

parmEnd.Value      will be equal to 05/05/2009 11:59:59 PM
parmEnd.SqlValue will be equal to 05/06/2009 00:00:00 AM

I fixed this by doing: SqlParameter parmEnd = new SqlParameter("@EndDateTime", EndDateTime.ToString());

This did take me awhile to find the issue in my code.
Sign in to post a comment.
Posted by Praneeta [MSFT] on 6/18/2013 at 1:37 PM
In the print function of the sqlDateTime, FromTimeSpan, there is some loss of precision and the milliseconds is rounded to 0.03 second. Which is why in this case the time is rounded to the next minute.
A workaround would be to subtract equal to or more than 1.5 milliseconds in the example given to recreate the bug, this gives the correct results.
Posted by Microsoft on 10/16/2012 at 9:52 PM
Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Microsoft Visual Studio Connect Support Team for triage and resolution. These specialized experts will follow-up with your issue.
Posted by Macy [MSFT] on 10/16/2012 at 2:50 PM
Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(