MS SQL Server: BETWEEN Conditions in SQL Server

In SQL Server (Transact-SQL), BETWEEN conditions help retrieve data in a range, in the SELECT, INSERT, UPDATE, or DELETE commands.

Syntax of BETWEEN conditions

 bieu_thuc BETWEEN giatri1 AND giatri2 ; 

Variable name or variable value

bieu_thuc

column or information field

giri1 and giatri2

values that make up the range that will be compared, including the two end values.

Note

The BETWEEN condition will return all records that bieu_thuc is between girth1 and giri2, including 2 end values.

As an example, - numerical value

 SELE CT * 
 FROM nhanvien 
 WHERE n hanvien_id BETWEEN 25 AND 100; 

The above example will return rows in the table if nhanvien_id is between 25 and 100 (including 25 and 100), equivalent to the SELECT statement below.

 SELEC T * 
 FROM nhanvien 
 WHERE nhanvien_id >= 25 
 AND nhanvien_i d <= 100; 

Example - with date

 SEL ECT * 
 FROM nhanvien 
 WHERE n gay_bat_dau BETWEEN '05/05/2014' AND '05/31/2014'; 

The above BETWEEN conditions return records from the table with the value immediately_bat_dau between May 1, 2014 and May 31, 2014 (including the first and last days). The above command is equivalent to the SELECT command below.

 SELEC T * 
 FROM nhanvien 
 WHERE ngay_bat_dau >= '2014/05/01' 
 AND ngay _bat_dau <= '05/05/2014'; 

Example - NOT operator

 SELE CT * 
 FROM nhanvien 
 WHERE nhanvien_id NOT BETWEEN 2000 AND 2999;