cast exception in SQL Server when querying derived table

Question

While trying to answer another SQL Server question on Stack Overflow I encountered something that does not work as I expected. I am using SQL Server 2008R2, but this is probably not important.

I have a simple hypothetical table that has some dates stored as VARCHAR datatype. I know it is obvious and practical to store date information using the DATE datatype, but this example uses a VARCHAR intentionally to demonstrate a problem I encountered. The reason the date can be invalid is not important - it could have been bad sanitization, malformed update query, use your imagination, etc.

-- setup
CREATE TABLE #temp (DateString VARCHAR(10)); 
INSERT #temp (DateString) VALUES ('01/01/2013');
INSERT #temp (DateString) VALUES ('02/14/2013');
INSERT #temp (DateString) VALUES ('03/31/2013');
INSERT #temp (DateString) VALUES ('05/27/2013');
INSERT #temp (DateString) VALUES ('06/31/201'); -- known invalid date, maybe the data wasn't sanitized, etc.
INSERT #temp (DateString) VALUES ('07/04/2013');

I want to select the number of holidays before July 1st, 2013. I suspect the dates may be invalid so I must plan for that to avoid exceptions. Before I even write these following queries, I know they will fail:

-- fails: cast exception, obviously
SELECT COUNT(*) AS [CountHolidays]
FROM #temp
WHERE CAST(DateString AS DATE)<'20130701';

-- fails: ISDATE() is not gauranteed to be evaluated first, less obvious, pointed out by another user.
SELECT COUNT(*) AS [CountHolidays]
FROM #temp
WHERE ISDATE(DateString) = 1 AND CAST(DateString AS DATE)<'20130701';

This query works as expected and would be my final choice:

-- works
SELECT COUNT(*) AS [CountHolidays]
FROM #temp
WHERE CONVERT(DATE,CASE WHEN ISDATE(DateString)=1 THEN datestring ELSE NULL END) < '20130701';

Before I wrote my final query though, I tried this first and I expected it to work, but it also raises a cast exception. Why does this query fail specifically?

-- Why does this query fail specifically?
-- I expected my derived inner query to filter invalid dates out first, but it does not. I get the same cast exception.
SELECT COUNT(*) AS [CountHolidays]
FROM (
    -- the derived table returns expected data when executed independently.
    SELECT DateString
    FROM #temp
    WHERE ISDATE(DateString) = 1
) AS T
WHERE CAST(DateString AS DATE)<'20130701';

Answer 1

SQL Server is clever enough to see what you're doing and combine the inner and outer queries to make it more efficient. You're going to have to force it to do two passes.

Use the inner query to select into a temporary table, and then use that in the outer query.

Answer 2

By doing explicit date conversion, you will get the result as you expected.

SELECT COUNT(*) AS [CountHolidays] 
FROM (SELECT DateString FROM #temp WHERE ISDATE(DateString) = 1) AS T
WHERE CAST(DateString AS DATE)<(select CAST('20130701' AS DATE));

The below query itself returns the result(empty) but it doesn't give the count. Because before that itself the date conversion gets failed and it displays the error message as "Conversion failed when converting date and/or time from character string." If you try with 'select *' you get the result and then the error message appears.

SELECT COUNT(*) AS [CountHolidays] 
FROM (SELECT DateString FROM #temp WHERE ISDATE(DateString) = 1) AS T
WHERE CAST(DateString AS DATE)<'20130701';

Depending on the connections language settings the date field can be interpreted differently. It is a SQL Server language dependent. The date format is interpreted differently in each language. so converting the date format explicitly works better. To check your language setting and other options use the below query.

DBCC USEROPTIONS