How to convert datetime string without delimiters in SQL Server as datetime?

Question

I am looking to convert a string like this: 20160520191959550 - that is actually date-time for 2016-05-20 19:19:59.

I tried using CAST as datetime in a SQL statement, but got this error:

Conversion failed when converting date and/or time from character string.

Here is the SQL:

Select Cast(vbon.DATE_INS As datetime) As DINS 
From vbon 

I routinely use CAST on date strings without delimiters and am looking for a similar solution. Is there a simple way to get the job done without reverting to LEFT, RIGHT and SUBSTRING?

Answer 1

Addtional variant to already posted:

SELECT  CONVERT(DATETIME, FORMAT(CONVERT(BIGINT, '20160520191959550'),
                                 '####-##-## ##:##:##"."###'))

Answer 2

In SQL Server 2012 and later, you can use DATETIMEFROMPARTS:

DECLARE @DateTimeString varchar(19) = '20160520191959550';
SELECT DATETIMEFROMPARTS(
      SUBSTRING(@DateTimeString,1,4)
    , SUBSTRING(@DateTimeString,5,2)
    , SUBSTRING(@DateTimeString,7,2)
    , SUBSTRING(@DateTimeString,9,2)
    , SUBSTRING(@DateTimeString,11,2)
    , SUBSTRING(@DateTimeString,13,2)
    , SUBSTRING(@DateTimeString,15,3)
    );

In earlier versions, one method is to build a ISO 8601 formatted string and use CAST or CONVERT:

SELECT CAST(
      SUBSTRING(@DateTimeString,1,4)
     + '-' + SUBSTRING(@DateTimeString,5,2)
     + '-' + SUBSTRING(@DateTimeString,7,2)
     + 'T' + SUBSTRING(@DateTimeString,9,2)
     + ':' + SUBSTRING(@DateTimeString,11,2)
     + ':' + SUBSTRING(@DateTimeString,13,2)
     + '.' + SUBSTRING(@DateTimeString,15,3)
    AS datetime2(3));