isdate 为何无法过滤掉汉字

[sofastcar]

有一列日期型数据（首建日期）和一列字符型数据（到期日），
其中“到期日”字段有一个“长期”汉字内容，其他内容是类日期型的字符串，形如“20180512”，“99990605”。
使用select a.*
from kehu a
where isdate(a.到期日)=1 and datediff（day,首建日期,到期日）<0
出现字符转日期错误

而用中间表如下 同样出现上述错误
select b.*

from (
select a.* from kehu a
where isdate(a.到期日)=1

) b
where datediff（day,b.首建日期,b.到期日）<0

而用
select a.* from kehu a
where isdate(a.到期日)=1
and a.到期日<>'长期'
and datediff（day,首建日期,到期日）<0

或
select b.*

from (
select a.* from kehu a
where isdate(a.到期日)=1
and a.到期日<>'长期'

) b
where datediff（day,b.首建日期,b.到期日）<0


语句正确，未提示错误，请问为什么呢？难道where isdate 不能过滤掉“长期”非法字符吗？临时表b为何不起作用？
隐形和显性（cast as date ）都无法转化

[二月十六]

select b.* from ( select a.* from kehu a where isdate(a.到期日)=1 ) b 看看查询的b.到期日 有不是日期的吗

[sofastcar]

select b.* from ( select a.* from kehu a where isdate(a.到期日)=1 ) b 查询到的结果不包含"长期"，但要用datediff时就必须如下才行 select b.* from ( select a.* from kehu a where isdate(a.到期日)=1 and a.到期日<>'长期' ) b where datediff（day,b.首建日期,b.到期日）<0

[zjcxc]

可以试试这样写条件 case when isdate(a.到期日)=1 then case when datediff(day,首建日期,到期日)<0 then 1 end end = 1

[zjcxc]

看执行计划，有出错的，isdate 的条件应该是放到后面处理了

[sofastcar]

select b.* from ( select a.* from kehu a where isdate(a.到期日)=1 ) b 感觉上述子查询 select a.* from kehu a where isdate(a.到期日)=1虽然没有包含“长期”，但是与外层的where datediff（day,b.首建日期,b.到期日）<0 连用时就会显示字符转日期错误。 除非子查询 select a.* from kehu a where isdate(a.到期日)=1 and a.到期日<>'长期' 增加a.到期日<>'长期'条件，觉得挺奇怪的！！！ 求教为何会发生这样情况？

[sofastcar]

判断isdate与datediff连用会把isdate 后置处理吗？

[zjcxc]

引用 6 楼 sofastcar 的回复:

判断isdate与datediff连用会把isdate 后置处理吗？

你可以直接看执行计划来验证

[shinger126]

引用 6 楼 sofastcar 的回复:

判断isdate与datediff连用会把isdate 后置处理吗？

看执行计划，SQL是先扫描表，然后计算标量，再通过筛选器，最后select输出列表。其中计算标量这步就开始计算datediff（day,首建日期,到期日)了。用a.到期日<>'长期' and datediff的话，执行的只有表扫描，没有计算标量

[mywisdom88]

楼主的意思是,他的字段"到期日"是字符的,里面存的有时候是日期字符,有时候是中文"长期"

[sofastcar]

是的，到期日列是字符型，八位，用隐性转化为日期型date,测试过，用显性转化cast as date 也会在datediff 环节提示字符转化为日期错误。

[吉普赛的歌]

楼主不是写不出正确的语句， 是想搞清楚怎么回事， 是这样吧？

先建立一个可以正常测试的脚本：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	dq NVARCHAR(20),
	sj DATETIME
)
SET NOCOUNT ON;
INSERT INTO t VALUES ( '20180512','20180513' )
INSERT INTO t VALUES ( N'长期','20180513' )
INSERT INTO t VALUES ( '99990605','20180513' )
GO
--错误
SET STATISTICS PROFILE ON
SELECT dq,sj FROM t a
WHERE 
1=1
--AND ISNUMERIC(a.dq)=1 
AND isdate(a.dq)=1 and datediff(day,sj,dq)<0

--正确
select dq,sj  from t a
where isdate(a.dq)=1 
      and a.dq<>'长期'
      and datediff(day,sj,dq)<0

错误的没有办法， 看不到， 还是拿正确的来看吧：


很明显：
第一步处理的是 <> ‘长期’ 和 datediff
第二步处理的才是 isdate

从执行过程的层面来说， 是理清楚了： isdate 的过滤， 相对其它条件来说是靠后的， 所以无法提前为其它条件的比较作出筛选。
至于为什么要这么安排， 这个就复杂了， 你可能要发个邮件给 SQL Server 的开发者。

[shinger126]

多简单的事，select * from kehu where 首建日期>cast(case when 到期日='长期' then '9999-12-31' else 到期日 end as datetime)

[xiaoxiangqing]

分开写语句。

[sofastcar]

引用 13 楼 yenange 的回复:

楼主不是写不出正确的语句， 是想搞清楚怎么回事， 是这样吧？ 先建立一个可以正常测试的脚本：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	dq NVARCHAR(20),
	sj DATETIME
)
SET NOCOUNT ON;
INSERT INTO t VALUES ( '20180512','20180513' )
INSERT INTO t VALUES ( N'长期','20180513' )
INSERT INTO t VALUES ( '99990605','20180513' )
GO
--错误
SET STATISTICS PROFILE ON
SELECT dq,sj FROM t a
WHERE 
1=1
--AND ISNUMERIC(a.dq)=1 
AND isdate(a.dq)=1 and datediff(day,sj,dq)<0

--正确
select dq,sj  from t a
where isdate(a.dq)=1 
      and a.dq<>'长期'
      and datediff(day,sj,dq)<0

错误的没有办法， 看不到， 还是拿正确的来看吧： 很明显： 第一步处理的是 <> ‘长期’ 和 datediff 第二步处理的才是 isdate 从执行过程的层面来说， 是理清楚了： isdate 的过滤， 相对其它条件来说是靠后的， 所以无法提前为其它条件的比较作出筛选。 至于为什么要这么安排， 这个就复杂了， 你可能要发个邮件给 SQL Server 的开发者。

请问你用的是什么工具看执行计划的？是ms的企业管理器吗？ 另外 看执行计划是从下往上执行吗？先scan table 再 filter ，最后select 吧？谢谢

[文盲老顾]

select a.*
        from kehu a
        cross apply (select 1 as tmpfield where isdate(a.到期日)=1 and datediff（day,首建日期,到期日）<0) b
       --where isdate(a.到期日)=1 and datediff（day,首建日期,到期日）<0 

[吉普赛的歌]

引用 14 楼 sofastcar 的回复:

[quote=引用 13 楼 yenange 的回复:] 楼主不是写不出正确的语句， 是想搞清楚怎么回事， 是这样吧？ 先建立一个可以正常测试的脚本：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	dq NVARCHAR(20),
	sj DATETIME
)
SET NOCOUNT ON;
INSERT INTO t VALUES ( '20180512','20180513' )
INSERT INTO t VALUES ( N'长期','20180513' )
INSERT INTO t VALUES ( '99990605','20180513' )
GO
--错误
SET STATISTICS PROFILE ON
SELECT dq,sj FROM t a
WHERE 
1=1
--AND ISNUMERIC(a.dq)=1 
AND isdate(a.dq)=1 and datediff(day,sj,dq)<0

--正确
select dq,sj  from t a
where isdate(a.dq)=1 
      and a.dq<>'长期'
      and datediff(day,sj,dq)<0

错误的没有办法， 看不到， 还是拿正确的来看吧： 很明显： 第一步处理的是 <> ‘长期’ 和 datediff 第二步处理的才是 isdate 从执行过程的层面来说， 是理清楚了： isdate 的过滤， 相对其它条件来说是靠后的， 所以无法提前为其它条件的比较作出筛选。 至于为什么要这么安排， 这个就复杂了， 你可能要发个邮件给 SQL Server 的开发者。

请问你用的是什么工具看执行计划的？是ms的企业管理器吗？ 另外 看执行计划是从下往上执行吗？先scan table 再 filter ，最后select 吧？谢谢[/quote] 1. 什么工具？ SSMS, 设置 SET STATISTICS PROFILE ON 其实上面的代码已经非常清晰了， 你照着执行就会有同样的结果。 2.这个执行计划怎么看？ 从下往上看。

[文盲老顾]

select a.*
        from kehu a
       where isdate(a.到期日)=1 and datediff（day,首建日期,到期日）<0 

单独运行这个指令，我这里没有提示出错，mssql2008