Use of between and in MSSQL server _mssql

Information Customer Id Name Registerdate 1 Peng Peng 2007/1/5 00:00:00 2 Tintin 2007/1/6 00:00:00 3 Asia 2007/1/7 00:00:00 ASPX pages: Minimum date: [2007-01-06] Maximum date: [2007-01-06] SQL to use: SELECT ID, Name, registerdate From Customer WHERE (registerdate BETWEEN ' 2007-01-06 ' and ' 2007-01-06 ') The result is a return of 1 strokes: Tintin, which meets my expectation. But what if Tintin's registerdate is not 00:00:00? If the information is like this Id Name Registerdate 1 Peng Peng 2007/1/5 00:00:00 2 Tintin 2007/1/6 04:37:00 3 Asia 2007/1/7 00:00:00 The same SQL SELECT ID, Name, registerdate From Customer WHERE (registerdate BETWEEN ' 2007-01-06 ' and ' 2007-01-06 ') It's a 0-pen return. Well, because I haven't given the right time range, my search terms should be the whole day of the 2007/1/6. So I usually start with a date plus 1 days before the procedure is checked. Registermaxdate = registermaxdate.adddays (1); But is that right?? The SQL that this program produces is SELECT ID, Name, registerdate From Customer WHERE (registerdate BETWEEN ' 2007-01-06 ' and ' 2007-01-07 ') Instead, they send back 2 of them, and they bring it back to Asia. Because Sql between Min and Max is the meaning of value >=min && value <= Max So I should be more precise. Registermaxdate = registermaxdate.adddays (1). AddSeconds (-1); In that way, the SQL generated will be SELECT ID, Name, registerdate From Customer WHERE (registerdate BETWEEN ' 2007-01-06 ' and ' 2007/1/6 23:59:59 ') 1, that is, Tintin, the information I want.

4.3.5 Limited data range--between

In the WHERE clause, using the BETWEEN keyword makes it easier to limit the range of query data. Of course, you can also use the not between keyword query to qualify records that are outside the range of data.

The syntax format can be represented as follows:

expression [NOT] BETWEEN expression 1 and expression 2

For example, select the number of ranges between 10~100 and the between operator can be expressed as between 100.

★ NOTE ★

When you use between to limit the query data range, you include boundary values, while queries using not between do not include boundary values.

Instance 20 using between to qualify a data range query

This example realizes in the BookInfo table, inquires the book price in the 35~60 between all books record. The code is as follows:

Use Library SELECT * From BookInfo WHERE Price BETWEEN and 60

Run the code, and the resulting query results are shown in Figure 4.21.

498) this.style.width=498; "Border=0>

Fig. 4.21 the record of the book price between 35~60 in the BookInfo table

As seen from the results, the Between keyword query contains boundary values.

In fact, the effect of querying with a between expression can be replaced entirely with a logical expression that contains ">=" and "<=", and the effect of using the not between query can be replaced with a logical expression that contains ">" and "<".

For instance 20, if you use a logical expression containing ">=" and "<=" instead of the between expression, the code is as follows:

Use Library SELECT * From BookInfo and Price <=60

Executing the code will result in the same query.

The between operator can also be used to compare time, and a concrete application example is given below.

Instance 21 qualifying a time range query with the between operator

This example is implemented in the BookInfo table, querying all book records between 2004/5/1 and 2006/1/1 for the publication date. The code is as follows:

Use Library SELECT * From BookInfo WHERE pubdate BETWEEN ' 2004/5/1 ' and ' 2006/1/1 '

Run the code, and the resulting query results are shown in Figure 4.22.

498) this.style.width=498;" BORDER=0>