Convert varchar to datetime and add seconds

Question

I have two columns startDate (160812 - year, month, day) and startTime (112345 - hour, mimutes, seconts) with a varchar datatype my target is concatenate them and convert them into datetime. And I should added to them other column (duration - int)

I tried something like this:

WITH [A] AS 
(
    SELECT (startDate + startTime) AS time1
    FROM [Date] 
 )
SELECT 
    CONVERT(datetime, A.time1, 20)
FROM 
    [A]  

however I get an error message:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Any better ideas to try?

Answer 1

I think you want something like this:

select (convert(datetime, startDate, 12) + 
        convert(time, stuff(stuff(startTime, 5, 0, ':'), 3, 0, ':')))
       ) as dt