Date Parameters passed to the stored procedure do not work properly

Question

There was a problem in my project when I transferred the date as 09/03/2013 23:59:59 to a stored procedure, but saw .net in the profiler, converted it to 09/04/2013 00:00:00 .

To confirm that I have created a small test application (anyone can use it for replication, I use .Net 4.5 express release and Sql server 2012).

Below is the test code:

 DateTime startdate = DateTime.Parse("09/03/2013"); DateTime endDate = startdate.AddDays(1).AddTicks(-1); try { using (SqlConnection konekcija = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString())) { konekcija.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = konekcija; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[Interface].[uspTestDateParameter]"; cmd.Parameters.AddWithValue("@CurrentDate", startdate); cmd.Parameters.AddWithValue("@BatchEndDate", endDate); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { // Fill the DataSet using default values for DataTable names, etc DataSet dataset = new DataSet(); da.Fill(dataset); DataTable dt = dataset.Tables[0]; //return dataset; } } } } catch (Exception ee) { } 

The following is the procedure:

 CREATE PROCEDURE [Interface].[uspTestDateParameter] ( @CurrentDate DateTime ,@BatchEndDate DateTime ) AS BEGIN Declare @table table (strt Datetime ,endT Datetime ) Insert into @table values (@CurrentDate,@BatchEndDate) Select * from @table END 

Returned result: 9/3/2013 12:00:00 AM 9/4/2013 12:00:00 AM

I could connect a screen shot of the Dataset visualizer, but I can’t do this, because it requires a reputable work of 10. But the above are the values ​​of the two columns (strt, enDt) that I get.

Can anyone help? Because of this, my procs do not work.

Answer 1

DATETIME rounded as described here:

http://technet.microsoft.com/en-us/library/ms187819.aspx

This article explicitly states that all values ​​are rounded to .000, .003, or .007 seconds. The user-specified DateTime 01/01/98 23:59:59.999 will always be stored as 1998-01-02 00:00:00.000 .

Instead of using ticks, why can't you do the following?

 DateTime endDate = startdate.AddDays(1).AddSeconds(-1); 

This would actually convey the date you said you were passing ( 09/03/2013 23:59:59 ), instead of one check mark following the next second.

As an alternative, use DATETIME2 as your SQL data type, which, according to the documentation, has an accuracy of 100 ns (one tick):

http://technet.microsoft.com/en-us/library/bb677335.aspx

Answer 2

The problem is rounding the SQL Server datetime type as described by AntP.

There are two different solutions that you might consider:

Option number 1

Use the datetime2 type in SQL Server, as Tim suggested. It has higher accuracy, so you can hardly round it. This is still tricky since you need to know how much accuracy you are sending and how much the type will support. In other words, should it be 23:59:59.999 or should it be 23:59:59.999999 or will it be 23:59:59.0 enough? You will need to decide what makes sense for your application.

If your data always contains integer dates, you can change your input value to:

 DateTime endDate = startdate.AddDays(1).AddSeconds(-1); 

And it will not be rounded, even with datetime type.

Option number 2

Use half-open intervals [start,end) . When the end date is exclusive, your inquiries are much simpler, and you do not need to worry about accuracy. When two intervals border each other, the end of one interval will be exactly the same as the beginning of the next. There is no ambiguity because the end date is exclusive.

Instead of sending the range from 09/03/2013 00:00:00 to 09/03/2013 23:59:59 you send it as 09/03/2013 00:00:00 at 09/04/2013 00:00:00 with the understanding that the exact end date is excluded.

In other words, the date is in the range if:

 StartOfRange <= @TheDate < EndOfRange 

Or add other terms:

 StartOfRange <= @TheDate AND EndOfRange > @TheDate 

On the .NET side, you can still imagine your baseline as fully inclusive. Just add the appropriate value to the end before passing it to SQL. For example, if you request integer dates as input, then add one day to the end.

Answer 3

Tick ​​is a very small unit of time ( MSDN ). In milliseconds, there are 10,000 ticks.

Try subtracting 1-2 seconds to see if it works.

Btw, SQL Server date and time can only store 333 milliseconds (for example: .000, .003.,. 006, etc.)