SQL Server - How To Converting Datetime Type From Nvarchar Type?

Question

I have got two columns of nvarchar.

Sample data is:

column1='20180402', column2='134259'

My goal is to create a datetime type column by combining these two columns.

Like this: '2018-04-02 13:42:59.000'

How can I do that ?

Please, help me ?

Answer 1

declare @d varchar(50)='20180402',
        @t varchar(50)='134259'


select convert(varchar(50),convert(date,@d)) + ' '+ convert(varchar(50),dateadd(hour, (@T / 10000) % 100,
       dateadd(minute, (@T / 100) % 100,
       dateadd(second, (@T / 1) % 100,
       cast('00:00:00' as time(3))))) )