Conversion failed when converting date and/or time from character string.

[Ken920454]

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) w

where 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() --在这里使用限制条件，太晚了。只有在临时表使用限制条件才没有错误。

[AcHerat]

select * from
(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

[Shawn]

CREATE TABLE table1
(
	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 * from
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) w

where cast(v.strField as datetime)< getdate()

lz的写法真纠结，有木有

[yubofighting]

v.dateField

[dacer2505]

select * from
(select cast(v.strField as datetime) as dateField from
(select strField from table1 where isdate(strField)=1) v) w

where 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) w

where cast(w.strField as datetime)< getdate()

[dacer2505]

发错了
where cast(v.strField as datetime)< getdate()
改为：
where w.strField < getdate()

[gogodiy]

1楼的写法很简洁，建议楼主采用。