Odd result using Between

Question

Odd result using Between

Andrew Gothard-467944

Hall of Fame

Points: 3928
More actions
October 15, 2008 at 9:52 am

#194683

Now this baffles me. Just been working on something checking on records between two dates;
SELECT DISTINCT
Column1,
Column2,
Count(Column3)
FROM
SomeTable
WHERE
Column1 BETWEEN '1 oct 2008' and '1 jul 2008'
GROUP BY Column1, Column2
returns no results, while
SELECT DISTINCT
Column1,
Column2,
Count(Column3)
FROM
SomeTable
WHERE
Column1 BETWEEN '1 jul 2008' and '1 oct 2008'
GROUP BY Column1, Column2
returns (the expected) number of rows.
Columns1 and 2 are both DateTime fields. What gives? This is certainly unexpected behaviour as far as I'm concerned (and conflicts with BOL - "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."). Anyone seen this type of behaviour before or got any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

"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.").

if you set your begin expression to a value > end_expression you would not get a true result.

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 2

!

It does say that, doesn't it. Wonder if :I've never tripped over that one before over the past almost 2 decades or what.

Not very intuitive though - it's a bit like saying

"Yes - Chester-Le-Street is between Durham and Sunderland but no, it's not between Sunderland and Durham" (from a geographical rather than alphabetical point of view, obviously)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Andrew Gothard (10/15/2008)
!
It does say that, doesn't it. Wonder if :I've never tripped over that one before over the past almost 2 decades or what.
Not very intuitive though - it's a bit like saying
"Yes - Chester-Le-Street is between Durham and Sunderland but no, it's not between Sunderland and Durham" (from a geographical rather than alphabetical point of view, obviously)

The difference is that in normal usage BETWEEN is associative (3 is between 1 and 5 = 3 is between 5 and 1) and in SQL it isn't.

😎