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 dateti