Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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)

share|improve this question
Both queries are invalid, unless there is an MTR column in the table. – ypercube Sep 11 '12 at 13:32
1  
On a, perhaps, slightly irrelevant note, make sure your TheDate value is not greater than TheOtherDate if you expect to receive non-negative results. I.e., DATEDIFF(M, '2012-02-20', '2012-05-15') evaluates to 3, but if you swap the dates, you'll get -3. – Andriy M Sep 11 '12 at 16:44
1  
The reason why you're getting values less than 0 is because of the OR in your WHERE clause, this should've been AND. Max's query below will work. – jsauni Sep 12 '12 at 10:23

2 Answers

up vote 2 down vote accepted

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)
share|improve this answer
1  
Will this work? I don't think there is any DBMS that allows an alias defined in the SELECT list to be used in WHERE clause. Not without wrapping in a derived table. – ypercube Sep 11 '12 at 6:15
my mistake. I'll correct the query to reflect the actual names of the fields. – Max Vernon Sep 11 '12 at 14:59
recalculating the datediff works - thanks! – mattw Sep 13 '12 at 1:55
up vote 2 down vote

Ypercube's comment is relevant. The reference to MTR in your WHERE clause should be flagged as invalid, according to the standard. But apart from that, which value of MTR do you think will ever make that OR evaluate to false ?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.