SQL Server 2005 'between' operator behavior - bug or feature?

Question

According to microsoft documentation, the behavior of the 'BETWEEN' operator should be a closed interval both on the left and the right side, however, the following code:

SELECT CASE WHEN (1 BETWEEN 1 AND 2) THEN 'YEAH' ELSE 'NO' END AS [closed in the left],
       CASE WHEN (1 BETWEEN 2 AND 1) THEN 'YEAH' ELSE 'NO' END AS [closed in the right]

shows otherwise. Can you reproduce it? What do you think?

Answer 1

The minimum value must always be specified first when using BETWEEN. The documentation you cited indicates that:

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.

so your second CASE is actually testing:

1 >= 2 AND 1 <= 1

Answer 2

The documentation you link to says:

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

so the results you are getting are correct. The BETWEEN is simply a shorthand.