将字符串20060102133059转换为datetime类型2006-01-02 13:30:59

Question

把字符串用substring()等函数转为'YYYY-MM-DD HH:MI:SS'的格式，然后再cast(xx as datetime)

Answer 1

declare @v varchar(30)
set @v = '20060102133059'
set @v =  left(@v,4)
         +'-'+substring(@v,5,2)
         +'-'+substring(@v,7,2)
         +' '+substring(@v,9,2)
         +':'+substring(@v,11,2)
         +':'+right(@v,2)
select cast(@v as datetime)

Answer 2

declare @var varchar(30)
set @var='20060102133059'
select cast(left(convert(datetime,left(@var,8),120),10)+' '+substring(@var,9,2)+':'+substring(@var,11,2)
         +':'+right(@var,2) as datetime)

Answer 3

convert(datetime,left(@var,8),120)能简单些.