select strField from table1 where isdate(strField)=1 -- v1返回的全是日期格式正确的数据select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v --w
返回的全是日期型的正确的数据
问题出现在select * from
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) wwhere w.dateField < getdate() --不加条件正确,一加条件就错误:Conversion failed when converting date and/or time from character string.现在我使用select cast(v.strField as datetime) as dateField INTO #TMPTABLE from
(select strField from table1 where isdate(strField)=1) v -- 结果正确,但是太多的记录插入到临时表里了。SELECT * FROM #TMPTABLE WHERE dateField < getdate() --在这里使用限制条件,太晚了。只有在临时表使用限制条件才没有错误。
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) w很纠结楼主这样子的写法,就当做是正常的语句,看看你的v那个别名放哪里去了,条件后边而且不是在查询语句的括号外不错才怪!建议楼主这么写select cast(strField as datetime) as dateField
from table1
where isdate(strField)=1
(
strField VARCHAR(30)
)
INSERT table1
SELECT CONVERT(VARCHAR(30), GETDATE()-1, 121) UNION ALL
SELECT '2011-1-32 00:00:01.001'
go
--SQL:
--原始SQL:
select * from
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) w
where w.dateField < getdate()
--优化器优化后的SQL(自己看一下执行计划):
SELECT *
FROM ( SELECT CAST(v.strField AS DATETIME) AS dateField
FROM ( SELECT strField
FROM table1
WHERE ISDATE(strField) = 1
AND CONVERT(VARCHAR(30), strField, 121) < GETDATE() --因为条件为AND,所以每行都会执行这个对比,第2行转换时出错
) v
) w
--试试下面SQL:
select * from table1
WHERE GETDATE() > (CASE WHEN isdate(strField)=1 THEN CONVERT(VARCHAR(30), strField, 121) END)
/*
2011-04-13 08:36:32.993
*/
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) wwhere cast(v.strField as datetime)< getdate() lz的写法真纠结,有木有
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) wwhere cast(v.strField as datetime)< getdate()
改为:
select * from
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) wwhere cast(w.strField as datetime)< getdate()
where cast(v.strField as datetime)< getdate()
改为:
where w.strField < getdate()