从字符串向 datetime 转换时失败的错误
- SQL code
DECLARE @Xml xmlset @Xml=N'<Chapter><CSN_SendTime>2012/3/10 11:54:40</CSN_SendTime></Chapter>'SELECT X.C.value('CSN_SendTime[1]','datetime') AS CSN_SendTime FROM @Xml.nodes('Chapter') AS X(C)消息 241，级别 16，状态 1，第 4 行
从字符串向 datetime 转换时失败。
[解决办法]
- SQL code
DECLARE @Xml xmlset @Xml=N'<Chapter><CSN_SendTime>2012/3/10 11:54:40</CSN_SendTime></Chapter>'SELECT X.C.value('CSN_SendTime[1]','varchar(100)') AS CSN_SendTime FROM @Xml.nodes('Chapter') AS X(C)
[解决办法]
- SQL code
DECLARE @Xml xmlset @Xml=N'<Chapter><CSN_SendTime>2012/3/10 11:54:40</CSN_SendTime></Chapter>'SELECT X.C.value('CSN_SendTime[1]','datetime') AS CSN_SendTime FROM @Xml.nodes('Chapter') AS X(C)
[解决办法]
注意2012/3/10 11:54:40长度，，把它放在一行就没有问题了
[解决办法]
- SQL code
DECLARE @Xml xmlset @Xml=N'<Chapter><CSN_SendTime>2012/3/10 11:54:40</CSN_SendTime></Chapter>'SELECT X.C.value('CSN_SendTime[1]','nvarchar(20)') AS CSN_SendTime FROM @Xml.nodes('/Chapter') AS X(C)CSN_SendTime--------------------2012/3/10 11:54:40(1 row(s) affected)
[解决办法]
{0}里面的回车符号去掉
[解决办法]
- SQL code
select cast( replace(replace('2012/3/10 11:54:40',char(13),''),CHAR(10),'') as datetime)
[解决办法]
- SQL code
DECLARE @Xml xmlset @Xml=N'<Chapter><CSN_SendTime>2012/3/10 11:54:40</CSN_SendTime></Chapter>'SELECT X.C.value('CSN_SendTime[1]','datetime') AS CSN_SendTime FROM @Xml.nodes('Chapter') AS X(C)
[解决办法]
select cast( replace(replace('2012/3/10
11:54:40',char(13),''),CHAR(10),'') as datetime)