如何将字符串转成日期时间格式？

Question

如：字符串 20090907084357怎样用SQL转成：
2009-09-07 08:43:57
格式？

Answer 1

select cast(left('20090907084357',8)+' '+substring('20090907084357',9,2)+':'+substring('20090907084357',11,2)+':'+substring('20090907084357',13,2)as datetime)-----------------------
2009-09-07 08:43:57.000(1 行受影响)

Answer 2

DECLARE @STR VARCHAR(100)
SET @STR='20090907084357'
SELECT @STR,CONVERT(DATETIME,LEFT(@STR,4)+'-'+SUBSTRING(@STR,5,2)+'-'+SUBSTRING(@STR,7,2)
+' '+SUBSTRING(@STR,9,2)+':'+SUBSTRING(@STR,11,2)+':'+SUBSTRING(@STR,13,2))

Answer 3

declare @PTime varchar(50)
select @PTime='20090909010100'
select  left(@PTime,4)+'-'+substring(@PTime,5,2)+'-'+substring(@PTime,7,2)+ ' '+substring(@PTime,9,2) + ':'+substring(@PTime,11,2) + ':'+substring(@PTime,13,2)
--------------------------------------------
2009-09-09 01:01:00

Answer 4

select cast(left('20090907084357',8)+' '+
substring('20090907084357',9,2)+':'+
substring('20090907084357',11,2)+':'+
substring('20090907084357',13,2)as datetime)-----------------------
2009-09-07 08:43:57.000(1 行受影响)