0

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?

ShoeLace
  • 3,476
  • 2
  • 30
  • 44
Kalkov
  • 39
  • 6
  • 1
    [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) – Jamiec Jan 06 '17 at 12:52

1 Answers1

1

I think you want something like this:

select (convert(datetime, startDate, 12) + 
        convert(time, stuff(stuff(startTime, 5, 0, ':'), 3, 0, ':')))
       ) as dt
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786