SQL Server Datetime Subquery Conversion Error?

Question

It is not possible to understand why when my subquery correctly filters out the data about bad dates (the user is entered in a real query), but the query fails when I return the results of the subquery (which has clear dates) back to datetime for where the item is. I have included a tableless example that fails. Spent a lot of time on this so far - hates life.

select date_test from ( select date_test from ( select '01/01/1980' as date_test union select 'a' ) as qry_bad_date where ISDATE(date_test) = 1 ) as qry_only_valid_date where cast(date_test as datetime) = '01/01/1980' 

Answer 1

If you use the Query Analyzer, go to the Query menu and select "Show Estimated Execution Plan" or press CTRL + L. Query Optimizer Sql Server decided that comparing date_test with the specified date refers to the food chain above. If you add an ISDATE check to your where statement, it will work fine:

 select date_test from (select date_test from (select '1980/01/01' as date_test union select 'a' ) as qry_bad_date where ISDATE(date_test) = 1 ) as qry_only_valid_date where ISDATE(date_test) = 1 and cast(date_test as datetime) = '1980/01/01' 

If you use temporary tables or variable tables to force queries, it also works:

 declare @dt1 table (date_test varchar(20)) declare @dt2 table (date_test varchar(20)) insert @dt1 select '1980/01/01' union select 'a' insert @dt2 select date_test from @dt1 where ISDATE(date_test) = 1 select date_test from @dt2 where cast(date_test as datetime) = '1980/01/01' 

Answer 2

This seems to work too. Thanks for the help!

 select date_test from ( select case isdate(date_test) when 1 then CAST(date_test as datetime) else null end as date_test from ( select '1980/01/01' as date_test union select 'a' ) as qry_bad_date where ISDATE(date_test) = 1 ) as qry_only_valid_date where cast(date_test as datetime) = '1980/01/01'