BETWEEN IN SQL Server

Question

How to get numbers from 4 to 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 on, you will want to use more 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, you will need to shorten the range:

 SELECT * FROM yourtable WHERE numbers between 5 AND 7 

see SQL Fiddle with Demo