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

2011-04-14 06:01:59 1.9k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Ken920454 墨衍会员 · AI 创作全网分发关注 + 加入社区

SQL日期时间处理避坑指南：类型选择、CAST转换与INTERVAL运算日期时间是SQL中最易出错的基础数据类型，其本质是带日历语义和时区上下文的领域概念，而非简单数值。不同数据库对DATETIME、TIMESTAMP WITH TIME ZONE等类型的实现存在显著差异，导致CAST转换失败、INTERVAL运算结果异常、时区偏移丢失等高频问题。技术价值在于通过合理选型（如优先使用DATETIME2(3)或TIMESTAMP WITH TIME ZONE）、安全转换（TRY_CAST兜底、AT TIME ZONE显式转换）和日历感知运算（DATEADD/INTERVAL专用函阅读详情

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

...全文

Ad hoc网络路由协议概述4——按需路由协议（2）AODV协议 (Ad-hoc on-demand distance vector algorithm protocol) AODV协议的基本流程。阅读详情

Ken920454

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

gogodiy 2011-04-14

打赏
举报

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

dacer2505 2011-04-14

打赏
举报

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

dacer2505 2011-04-14

打赏
举报

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

yubofighting 2011-04-14

打赏
举报

v.dateField

让你望见影子的墙 2011-04-14

打赏
举报

Shawn 2011-04-14

打赏
举报

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

*/

AcHerat 元老 2011-04-14

打赏
举报

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

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

相关推荐