SQL Server 2005 'between' statement behavior '- error or function?

Question

According to microsoft documentation, the behavior of the BETWEEN operator must be a closed interval both on the left and on the right, 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 differently. Can you reproduce it? What do you think?

Answer 1

The minimum value must always be specified when using BETWEEN . The documentation you provided 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 refer to says:

BETWEEN returns TRUE if test_expression is greater than or equal to begin_ and less than or equal to end_expression.

therefore, the results you get are correct. BETWEEN is just an abbreviation.

Answer 3

It should also indicate somewhere (I did not read completely) that the value on the left should be less than or equal to the value on the right. You should not provide a backlink just because the documentation may not indicate clearly enough to you. :-)