TSQL SELECT Convert or Cast DateTime but WHERE fails

Can a select return when a where fails?

This zen like question came up this afternoon while digging through some rather raw varchar table data this afternoon. The answer is yes and part of answer is coming up with the right question, I ended up settling on "CONVERT fails in where clause but not in select".

Consider the following problem:

SELECT * FROM dbo.MyTable
WHERE ISDATE(value)=1 AND AND  CAST(Value AS datetime) > GETUTCDATE()

--OR

SELECT * FROM dbo.MyTable
WHERE ISDATE(value)=1 AND CONVERT(datetime,Value) > GETUTCDATE()

Value
-----------------------
2012-01-02 00:00:00.000

--BUT

SELECT CONVERT(datetime,Value) FROM dbo.MyTable
WHERE ISDATE(value)=1

--OR

SELECT CAST(Value AS datetime) FROM dbo.MyTable
WHERE ISDATE(value)=1

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

--WTF?

To paraphrase the link above SQL Server may evaluate rows outside of the expected WHERE clause based on how the optimizer decides to limit the result set. This left me with three solutions:

1. Create an index to persuade the optimizer to avoid the alternate plan that evaluates non date columns (query condition that would allow you to do this not shown in examples. I am sure you can find one if you join a few things and look at the plan.)


2. Reload cast or converted data into a #temporary table


3. My solution below via path of least resistance add some case logic around the value column

SELECT Value FROM dbo.MyTable
WHERE CASE ISDATE(value)=1 THEN CONVERT(datetime,Value)
ELSE NULL END > GETUTCDATE()
--OIC :)

When you think about how SQL Server has to discover the table data it makes sense. If you haven't run into the condition before it may cause a little head scratching. Hopefully with this post and the corresponding stackoverflow post you can save a few extra hairs on your head.