BETWEEN is used for numbers and dates to set a maximum and minimum value. It is the equivalent of saying the value is greater than or equal to the minimum value and less than or equal to the maximum value which would look like ‘field1 >= minValue AND field1 <= maxValue’. The BETWEEN statement is easier to read and in most cases runs faster since it is an internal operation. Finally BETWEEN is the easiest way to accurately grab a date range. It can be provided with two datetime values and everything between them will be returned.
|BETWEEN||number1 BETWEEN 1 AND 10||number1 is between 1 and 10 (including both)|
|BETWEEN||date1 BETWEEN ‘11/1/2012’ AND ‘11/2/2012’||date1 is between the two provided dates. These values can be any datetime format|