Concatenate Date and Time Column of Character DataType Into DateTime DataType

Once I run the query how do I put them back together again?

I was able to run the following query to convert date value into datetime and append time part to it

declare @date char(8), @time char(8)

select @date='20101001',@time ='12:10:47'

select cast(@date as datetime)+@time

In the above method, date value is converted to datetime datatype and time value is added to it.

--------------Output ----------------------

result tab -

(No column name )

row1 || 2011-09-16 22:16.000

How can I covert back to the original data Value(undo)??????

I ran the above query to converted to d开发者_运维问答atetime datatype and time value is added to it- worked well...Now I want to undo go back to the original date value.....

---

It is not clear what the question is but this is my guess. If you are trying to extract pieces of a datetime use the DatePart funciton,

    declare @date char(8), @time char(8)

    select @date='20101001',@time ='12:10:47'

    select cast(@date as datetime)+@time

    select cast(cast(@date as datetime)+@time as datetime) 

    select DATEPART(mm,cast(cast(@date as datetime)+@time as datetime))

---

To extract the constituent parts of a datetime into a string of a specific format use the CONVERT function and pass the desired style. To get back to where you started use

DECLARE @date CHAR(8),
        @time CHAR(8)

SELECT @date = '20101001',
       @time = '12:10:47'

DECLARE @dt DATETIME

SELECT @dt = CAST(@date AS DATETIME) + @time

SELECT CONVERT(CHAR(8), @dt, 112) AS [@date],
       CONVERT(CHAR(8), @dt, 108) AS [@time]  

Which gives

@date    @time
-------- --------
20101001 12:10:47