SQL Server日期时间子查询转换错误？

Question

无法理解为什么当我的子查询正确过滤出错误的日期数据（用户输入的是真实查询），但是当我将子查询的结果（干净日期）转换回where子句的日期时间时，查询失败。我列出了一个无法显示的示例。花了很长时间在这个迄今为止讨厌的生活。

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

如果您使用查询分析器，进入查询菜单，选择“显示估计的执行计划”，或按CTRL + L。 Sql Server的查询优化器已经决定，比较date_test到你指定的日期在食物链上属于更高的。如果添加ISDATE检查您的where子句中正常工作：

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' 

如果使用临时表或表变量来强制查询执行单独它也可以工作：

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

貌似这个也起作用。感谢所有的帮助！

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'