Posted by Erland Sommarskog on 03/08/07 22:54

(Luke.Schollmeyer@gmail.com) writes:
> 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?

To add to Dan's post, there is only one way to control the order of
evaluation, and that is the CASE expression:

AND datediff(uuu, b.someDateField, getdate() BETWEEN
CASE WHEN a.lowboundary NOT LIKE '%[^0-9]%' THEN
a.lowboudnary
END AND
CASE WHEN a.highboundary NOT LIKE '%[^0-9]%' THEN
a.highboudnary
END


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]