字符串转换为时间的问题

[成都清香白莲]

像这样的字符串20020421150943171怎样转换为时间类型，如果只要前面的20020421可以转，但整个字符串就不行

[klan]

select convert(datetime,left(col1,8)) from tablea

[子陌红尘]

declare @str varchar(100)
set @str = '20020421150943171'
select cast(left(@str,8)+' '+substring(@str,9,2)+':'+substring(@str,11,2)+':'+substring(@str,13,2)+'.'+right(@str,3) as datetime)

[子陌红尘]

declare @str varchar(100),@var varchar(100)

set @str = '20020421150943171'
set @var = left(@str,8)+' '+substring(@str,9,2)+':'+substring(@str,11,2)+':'+substring(@str,13,2)+'.'+right(@str,3)

select cast(@var as datetime)

[vivianfdlpw]

declare @str varchar(100)
set @str = '20020421150943171'
select convert(datetime,
substring(@str,1,4)+'-' --年
+substring(@str,5,2)+'-' --月
+substring(@str,7,2) --日
+' '
+substring(@str,9,2)+':' --小时
+substring(@str,11,2)+':' --分钟
+substring(@str,13,2)+'.' --秒
+substring(@str,15,3) --毫秒
)

[wgsasd311]

declare @str varchar(30)
set @str='20020421150943171'
select
dateadd(ms,cast(right(@str,3)as int),
dateadd(ss,cast(substring(@str,13,2)as int),
dateadd(mi,cast(substring(@str,11,2)as int),
dateadd(hh,cast(substring(@str,9,2)as int),convert(datetime,substring(@str,1,8)) )
)
)
)
/*
--------------------------
2002-04-21 15:09:43.170

（所影响的行数为 1 行）
*/