Posted by Luke.Schollmeyer on 03/08/07 04:23

I found an unusual problem between 2000 and 2005 I haven't been able
to decipher from any documentation.

The query structure is as follows:

select *
from
tableA a
join
tableB b ON a.somekey = b.somekey
where
a.type = 'A'
and datediff(yyyy, b.someDateField, getdate()) between
a.lowboundary and a.highboundary

Some basic facts about the elements and data. The low and high-
boundary fields are varchar datatypes. In 2005 (regardless of
compatibility type I run the database under), the query evaluates the
BETWEEN and errors out due to the fact that it is evaluating the
DATEDIFF as an integer and finds a non-integer entry in either
lowboundary or highboundary. I understand and expect this behavior.
Obviously, changing the result of the DATEDIFF function to varchar
allows the operation to go forth.

The odd thing is that there is no "a.type = 'A' " entry, thus the
query wouldn't return anything. In 2000, it seems as though the
engine is evaluating the type = 'A' and short-circuiting and in 2005,
it is trying to evaluate the entire query OR is there an implicit
conversion occuring in 2000 and not in 2005?

As I mentioned, the compatibility mode doesn't change how this reacts,
but running this on a native 2000 server allows this to happen. This
particular code isn't the problem, it's what we might have to contend
with when we migrate this through. Sure, we're going to perform
regression testing, but I'm concerned about what we would miss.

Thanks for any replies.

[Back to original message]