请教大家一个问题。
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 #