请教一个比较奇怪的问题。
请教大家一个问题。
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的优化器原因，这两个语句的执行计划不同
稳妥写法：

SQL code

select * INTO #from Sys_Dictionarywhere 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())=1DROP TABLE #