patternsqlMinor
WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN
Viewed 0 times
greaterwherethanbetweenlessandnotclausefiltering
Problem
I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong?
Returns results that contain values inside and outside of the bounds of MTR being between 1 and 4. (from -3 to 75)
Alternately
Is also returning results that are 0, -2, -3, and -4 - but nothing higher than 0 and less than 4 (except for the entries that are 0)
SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
AND MTR >= 0 OR MTR <= 4Returns results that contain values inside and outside of the bounds of MTR being between 1 and 4. (from -3 to 75)
Alternately
SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
and MTR BETWEEN 0 AND 4Is also returning results that are 0, -2, -3, and -4 - but nothing higher than 0 and less than 4 (except for the entries that are 0)
Solution
The correct syntax for this would be:
SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR
FROM MyTable
WHERE MyID = 2074163 AND (
DATEDIFF(M, TheDate, TheOtherDate) >= 0 AND
DATEDIFF(M, TheDate, TheOtherDate) <= 4
)Code Snippets
SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR
FROM MyTable
WHERE MyID = 2074163 AND (
DATEDIFF(M, TheDate, TheOtherDate) >= 0 AND
DATEDIFF(M, TheDate, TheOtherDate) <= 4
)Context
StackExchange Database Administrators Q#24074, answer score: 3
Revisions (0)
No revisions yet.