34,594
社区成员
发帖
与我相关
我的任务
分享
[quote=引用 5 楼 u013331731 的回复:] [quote=引用 1 楼 ky_min 的回复:]直接字符串处理,或者写个函数包装处理了下DECLARE @STR VARCHAR(20) SET @STR='20150302123426000' SELECT CAST(LEFT(@STR,8)+' '+SUBSTRING(@STR,9,2)+':'+SUBSTRING(@STR,11,2)+':'+SUBSTRING(@STR,13,2)+'.'+SUBSTRING(@STR,15,3)AS DATETIME)
[quote=引用 1 楼 ky_min 的回复:]直接字符串处理,或者写个函数包装处理了下DECLARE @STR VARCHAR(20) SET @STR='20150302123426000' SELECT CAST(LEFT(@STR,8)+' '+SUBSTRING(@STR,9,2)+':'+SUBSTRING(@STR,11,2)+':'+SUBSTRING(@STR,13,2)+'.'+SUBSTRING(@STR,15,3)AS DATETIME)
第一种写法是对的,select convert(datetime,'212212250409',120) 转换错误。
你确定你格式没错?
你这样转换直接回出错的,我这样试过 http://www.w3school.com.cn/sql/func_convert.asp
http://www.w3school.com.cn/sql/func_convert.asp
select convert(datetime,'212212250409',120) 120是Style ID,参照 http://www.w3school.com.cn/sql/func_convert.asp
直接字符串处理,或者写个函数包装处理了下DECLARE @STR VARCHAR(20) SET @STR='20150302123426000' SELECT CAST(LEFT(@STR,8)+' '+SUBSTRING(@STR,9,2)+':'+SUBSTRING(@STR,11,2)+':'+SUBSTRING(@STR,13,2)+'.'+SUBSTRING(@STR,15,3)AS DATETIME)
select left('20150302123426000',4)+'-'+substring('20150302123426000',5,2)+'-'
+substring('20150302123426000',7,2)+' '+substring('20150302123426000',9,2)+':'+substring('20150302123426000',11,2)+':'
+substring('20150302123426000',13,2)+'.'+substring('20150302123426000',15,3)
DECLARE @STR VARCHAR(20)
SET @STR='20150302123426000'
SELECT CAST(LEFT(@STR,8)+' '+SUBSTRING(@STR,9,2)+':'+SUBSTRING(@STR,11,2)+':'+SUBSTRING(@STR,13,2)+'.'+SUBSTRING(@STR,15,3)AS DATETIME)
直接字符串处理,或者写个函数包装处理了下