Posted by Gert-Jan Strik on 07/08/05 18:37

No, an SQL Statement is a specification of the result, and
theoretically, it is created "all at once". It is not good enough that
you will get correct results if the query is executed "left to right,
top to bottom", because that is not how SQL works.

A solution for your problem could be to place the two related predicates
in a case expression. A case expression always has to be evaluated from
left to right.

select * from tempdb..test
where CASE WHEN isdate( coldate ) = 0 THEN 0
WHEN convert( datetime, coldate ) > GETDATE() THEN 1
ELSE 0 END = 1

Hope this helps,
Gert-Jan


Patrick Fiche wrote:
>
> Thanks for the answer.
> However, I thought that the optimizer was designed only to optimize the
> query but resultset was independant of the order of execution of
> filters.
> Here, it appears that depending on the order of application of filters,
> result is not the same....
> I guess that this is a special case due to the abuse of conversion
> use...
>
> Thanks
> Patrick
>
> *** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]