SQL Server 2008 : Varchar to Datetime conversion

Question

I have a datetime column ArrivalDateTime which is stored as a varchar value.

Let's say if the value is 20161212093256, I want the output to be 2016-12-12 09:32:56.

I could get the date part in datetime format as below.

SELECT 
    CONVERT(DATETIME2(0), LEFT('20161212093256', 8))

This returns the output as 2016-12-15 00:00:00.

I tried the following query to get the time part as well.

SELECT 
    CONVERT(DATE, LEFT('20161212093256', 8)) + ' ' + 
    CONVERT(TIME, RIGHT('20161212093256', 6))

But this throws an error:

The data types date and varchar are incompatible in the add operator

How can I get both date and time part in datetime format?

Answer 1

Get the date component first and convert it to DATETIME and then get the time component and convert it to DATETIME also. Finally, add the two results:

SELECT 
    CONVERT(DATETIME,LEFT('20161212093256', 8)) +
    CONVERT(DATETIME,
        LEFT(RIGHT('20161212093256', 6), 2) + ':' +
        SUBSTRING(RIGHT('20161212093256', 6), 3, 2) + ':' +
        RIGHT(RIGHT('20161212093256', 6), 2)
    )

---

To further explain, the result first conversion is the date component:

2016-12-12 00:00:00.000

The second conversion is the time component, but when you convert it to DATETIME it adds it to the 0 date or '1900-01-01', so the result is:

1900-01-01 09:32:56.000

Then, you add both DATETIMEs to get:

2016-12-12 09:32:56.000

---

To get rid of the ms component:

SELECT 
    CONVERT(DATETIME,LEFT('20161212093256', 8)) +
    CONVERT(DATETIME,
        LEFT(RIGHT('20161212093256', 6), 2) + ':' +
        SUBSTRING(RIGHT('20161212093256', 6), 3, 2) + ':00'
    )