请教一个比较奇怪的问题。

[专注研发ERP低代码开发平台]

请教大家一个问题。
select * from
(
select * from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1
) A
where datediff(d,cast(left(right(DID,11),8) as datetime),getdate())=1

报“从字符串向 datetime 转换时失败。”的错误


但改成
select * from
(
select top 100 * from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1
) A
where datediff(d,cast(left(right(DID,11),8) as datetime),getdate())=1

就不报错了。
select * from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1
中一共才41条数据，请问这是怎么回事呢？

[昵称被占用了]

SQL的优化器原因，这两个语句的执行计划不同
稳妥写法：

select * 
INTO #
from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1  

select * from  
# A
 where datediff(d,cast(left(right(DID,11),8) as datetime),getdate())=1

DROP TABLE #

[专注研发ERP低代码开发平台]

太奇怪了
select cast(left(right(DID,11),8) as datetime) as ADID from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1
不报错，但
select * from
(
select cast(left(right(DID,11),8) as datetime) as ADID from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1
) A
where datediff(day,ADID,getdate())=1

就报错了。

[昵称被占用了]

这样可能简单点，也不报错

select * from Sys_Dictionary
where len(DID)>11 and isdate(left(right(DID,11),8))=1  
AND left(right(DID,11),8) = CONVERT(VARCHAR(8),DATEADD(DAY,-1,GETDATE()),112)

[专注研发ERP低代码开发平台]

这个确实不报错，谢谢了