HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

Submitted by: @import:stackexchange-dba··
0
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?

SELECT 
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable 
WHERE MyID = 2074163
AND MTR >= 0  OR MTR <= 4


Returns 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 4


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)

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.