SQL Server FAQ - BETWEEN - Testing Value in a Range

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

What To Test Value Ranges with the BETWEEN Operator?

Sometimes you want to compare a value against a value range. You can do this with two regular comparison operations. But you can also use the special comparison operator BETWEEN to get it done with the following syntaxes:

1. Inclusively in the range test
test_value BETWEEN range_start_value AND range_end_value

- Returns the same results as the following expression
test_value >= range_start_value 
AND test_value <= range_end_value

2. Exclusively out of the range test
test_value NOT BETWEEN range_start_value AND range_end_value

- Returns the same results as the following expression
test_value < range_start_value 
OR test_value > range_end_value

Here are two examples of using the BETWEEN operator:

DECLARE @my_age INT;
SET @my_age = 17;
SELECT CASE WHEN 
      @my_age BETWEEN 11 AND 19
   THEN 'You are a teenager.'
   ELSE 'You are not a teenager.'
   END;
GO
You are a teenager.

DECLARE @my_age INT;
SET @my_age = 27;
SELECT CASE WHEN 
      @my_age NOT BETWEEN 11 AND 19
   THEN 'You are not a teenager.'
   ELSE 'You are a teenager.'
   END;
GO
You are not a teenager.

(Continued on next topic...)