SQL Server日期时间子查询转换错误？ [英] SQL Server Datetime Subquery Conversion Error?

问题描述

无法理解为什么当我的子查询正确过滤出坏的日期数据（用户在实际查询中输入），但是当我将子查询（其有干净的日期）的结果转换为日期时间条款。我包括一个失败的tableless示例。在这么远的生活中花了很长时间。

Can't comprehend why when my subquery properly filters out bad date data (user entered in real query) but that the query fails when I cast the results of the subquery (which has clean dates) back to a datetime for the where clause. I've included a tableless example which fails. Spent a long time on this thus far - hating 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'

推荐答案

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

If you're using Query Analyzer, go to the Query menu and select 'Display estimated execution plan', or hit CTRL+L. Sql Server's query optimizer has decided that comparing date_test to your specified date belongs higher on the food chain. If you add the ISDATE check to your where clause it works 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 temp tables or table variables to force the queries to execute separately 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'

这篇关于SQL Server日期时间子查询转换错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！