You might take a look at the query plan to see if for
some strange reason both the isdate() and cast()
expressions are being evaluated together.
But since there is nothing unusual about 2027-06-30, assuming
your dateformat setting is
Unfortunately for you, the too-clever optimizer decides it
can run the query faster if it restricts rows from property
based on the > filter before applying the isdate() filter.
SQL is a descriptive language, and while your query describes
a logical way to proceed with obtaining results, the optimizer
is free to get them in another fashion.
The solutions that I can think (aside from using datetime,
which is the best one) are these:
Change the derived table to
(select top 100 percent ... order by <choose your clustered index
columns for speed>)
This forces the derived table to be completely materialized
before the outer filter is applied. And in fact, just in case
someone decides to drop instances of top 100 percent for
efficiency sake, you might use top 99.99999999999999
percent, which works, although it's undoented (there you
risk someone fixing things so the 99.999... is truncated to an int
instead of used as is).
You can also change the final condition to:
case when isdate(lease_end) = 0 then 0
when lease_end > '1/1/2005' then 1
end = 1
Either of these may slow things down, but they should avoid
I also recommend using the format 'YYYYMMDD'
instead of a language or region specific format in the
query: ... > '20050101'
-- Steve Kass
-- Drew University
-- Ref: BF3CE58D-C663-41E7-88F5-0E0528DAEA8B