BETWEEN in SQL Server

Question

How do I get the numbers between 4 and 8. The answer I want is 5,6,7 but the script returns 4,5,6,7,8 when used in SQL Server:

SELECT * 
FROM table
WHERE numbers BETWEEN '4' AND '8'

Answer 1

BETWEEN is inclusive. From MSDN:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

Since it is inclusive you will want to use greater than and less than:

SELECT * 
FROM yourtable
WHERE numbers > 4
   AND numbers < 8

See SQL Fiddle with Demo

If you want to use the BETWEEN operator then you would need to shorten the range:

SELECT * 
FROM yourtable
WHERE numbers between 5 AND 7

see SQL Fiddle with Demo