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

Question

How can I convert VARCHAR data like '20130120161643730' to DATETIME ?

CONVERT(DATETIME, '20130120161643730') does not work.

However, CONVERT (DATETIME, '20130120 16:16:43:730') works. I guess it needs data in correct format.

Is there a valid way that can be used to convert to DATETIME directly from unformatted data ?

My solution is :

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 for a compact solution.

Answer 1

You can make it a little more compact by not forcing the dashes, and 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