Convert varchar data to datetime in SQL server when source data is w/o format -

how can convert varchar data '20130120161643730' datetime ?

convert(datetime, '20130120161643730') not work.

however, convert (datetime, '20130120 16:16:43:730') works. guess needs data in correct format.

is there valid way can used convert datetime directly unformatted data ?

my solution :

declare @var varchar(100) = '20130120161643730'  select concat(left(@var,8),' ',substring(@var,9,2),':',substring(@var,11,2),':',substring(@var,13,2),':',right(@var,3)) 

it works fine. however, i'm looking compact solution.

you can make little more compact not forcing dashes, , using stuff instead of substring:

declare @var varchar(100) = '20130120161643730';  set @var = left(@var, 8) + ' '    + stuff(stuff(stuff(right(@var, 9),3,0,':'),6,0,':'),9,0,'.');  select [string] = @var, [datetime] = convert(datetime, @var); 

results:

string                  datetime ---------------------   ----------------------- 20130120 16:16:43.730   2013-01-20 16:16:43.730