Precautions for using between and SQL Server

Today we also encountered the following problems. You should pay attention to using between in the future. It is best not to use it!

1. Problem:

Five pieces of data are as follows:
12:16:10. 000
2010-12-02 13:11:28. 000
2010-3 3 13:49:15. 000
2010-4 4 13:52:28. 000
13:52:28. 000

I used select * from table where [time] Between '2017-12-1 'and '2017-12-6' to display these five items.

If I use select * From sk_smssee where [time] Between '2017-12-1 'and '2017-12-5'
Why are four entries displayed?
12:16:10. 000
2010-12-02 13:11:28. 000
2010-3 3 13:49:15. 000
2010-4 4 13:52:28. 000

In other words, the last date is always not displayed. Why?

 

After the test code is changed to select * from table where [time]> = '2017-12-1 12:00:00 'and [time] <= '2017-12-5 12:00:00', four results are also displayed.. Since the database contains data for the period from 2010, why can't I find the data for [time] <= '2017-12-5?

2. The answer is as follows:

Using bewteen... In the ms SQL Server System and its scope is closed, that is, containing left and right boundary values.

In addition, in ms SQL System, when implicit conversion of date --> datetime is performed, the system automatically adds the time field 00:00:00.

That is, when the value is implicitly converted from the date type "" to the date type "00:00:00"

So use between for the date type... and... that is, only the partial score of the other edge value is actually included. You can use the semi-open and semi-closed intervals (>=& & <) to implement the problem you mentioned.