Some considerations for MS SQL Server keyword between

Write about the search function in the program in the near period of time. There is the keyword between, which is used to MS SQL Server, which is the data between the search range of values (including itself).

There are some things to be aware of when using it. If the time data.

Search time data, to see the database storage time accuracy, storage only date or with a time date needs to be noted. Otherwise the data that is being searched has some discrepancy with the expected.

Such as:

According to the above data, if the search date 17th to 18 data, you can write the conditions as follows:
... WHERE [Date] between ' 2016-10-17 ' and ' 2016-10-18 '. In fact, it can be equal to the following wording:
... WHERE [Date] >= ' 2016-10-17 ' and [date] <= ' 2016-10-18 '.
Because between is the data that gets between the range of values (including itself). But if not between, the data for the boundary is not included.
Search results, all data 17 and 18th will be searched.

Ok, let's look at some additional data:


When searching for this information above, if you use the above conditions:
... WHERE [Date] between ' 2016-10-17 ' and ' 2016-10-18 '.
You will find that only the data of number 17th can be searched, no data of number 18th, unless there is just one time: 2016-10-18 00:00:00:000, can only search for the pen number 18th. To check the cause, you need to refer to its equal syntax:
... WHERE [Date] >= ' 2016-10-17 ' and [date] <= ' 2016-10-18 ', it also equals:
... WHERE [Date] >= ' 2016-10-17 00:00:00:000 ' and [date] <= ' 2016-10-18 00:00:00:000 '
After looking at the last equivalent, you'll see why it only searches for 17 of all the data.
The problem has come out, so how can we solve it? If you are searching for time data, it is handled according to its accuracy. Only date, no time, big can rest assured between to search its range.
When time data has a date and time, it is recommended to use a combination of greater than equals (>=) and less than (<) and an end date plus one day.
As follows:
WHERE [Date]>=' 2016-10-17 ' and [Date]<DATEADD (day,1, ' 2016-10-18 ')

In fact, Insus.net has previously written related between related blog posts, reference:
How to query MS SQL Server with Time Records http://www.cnblogs.com/insus/p/3800587.html
In the between use of this article, there is also a day minus 2 seconds on the end time. Other This is also incorrect, here is the corrective treatment method.

There is one more article:
How to implement search function on Web site http://www.cnblogs.com/insus/archive/2011/03/30/1999795.html
When the condition is spliced, it is only defined to 59 seconds, so when the data contains microseconds, it is not searchable.

In any case, minus seconds is the wrong way to handle it. Or the accuracy of the time problem.

Check out the MSDNtime Section (DATEPART)Differences between SQL Server versions:


Summarize:
Search time data, need to put a universal rule, or less with between, on the end date plus one day, and use less than (<) logic conditions.

Some considerations for MS SQL Server keyword between