Problem with between

Question

Post reply

Problem with between

mwelcome

Right there with Babe

Points: 769
More actions
July 21, 2004 at 5:21 pm

#112090

I had a problem getting between to work correctly. Example:
case
when datediff(ss, x, y) between -3 and 3 then 'Y'
else 'N'
end
does not work as expected.
I changed to:
case
when datediff(ss, x, y) >= -3 and datediff(ss, x, y) <= 3 then 'Y'
else 'N'
end
This works fine. Is there some problem with between not working correctly with negative values? or am I missing something very basic?

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

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

I have seen this before!

BETWEEN perfroms an Inclusive Range where as > and < perfroms an Exculsive Range.

I haven't really made sense of this myself but thats the way it works!

Kindest Regards,

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 2

Yeah, I've seen this too for the case posted ...

It seems as if though SQL Server is expecting the start_value and end_value of BETWEEN to be the same sign ...

So, for the BETWEEN to work for the case posted, it would need to be broken into two BETWEENs:

CASE

WHEN DATEDIFF(ss, x, y) BETWEEN -3 AND -1 OR DATEDIFF(ss, x, y) BETWEEN 0 AND 3 THEN 'Y' ELSE 'N' END

I always try to use the >= and <= operators for ranges whenever possible.

JP

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

CASE WHEN ABS(DATEDIFF(ss, x, y) ) <= 3
THEN 'Y'
ELSE 'N'
END

Don't forget that DATEDIFF will always TRUNCATE to the whole second.
-2.9 seconds becomes -2 and 2.9 seconds becomes 2.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)