concatenate two columns with date and time in sql?

Question

I have two columns as orderdate(03/02/2011) and ordertime(10.34 am) in which i have to concatenate these two columns and show it in another column as datetime values(03/02/2011 10:34:16.190)....

any suggestion?

Answer 1

In general, the solution is to add days to the time value which has zero for its day portion. You are not clear as the data types of the two values, however one solution would be:

With Inputs As
    (
    Select '20110302' As DateVal, '10:34 AM' As TimeVal
    )
Select DateAdd(d, DateDiff(d, 0, Cast(DateVal As datetime)), Cast(TimeVal as datetime))
From Inputs 

A more explicit version assuming the inputs are strings and given your exact inputs:

Set DateFormat MDY

With Inputs As
    (
    Select '03/02/2011' As DateVal, '10:34 AM' As TimeVal
    )
Select DateAdd(d, DateDiff(d, 0, Cast(DateVal As datetime)), Cast(TimeVal as datetime))
From Inputs

Answer 2

Assuming you are working with a DATE and a TIME, you need to convert before you can add.

SELECT [orderdate] + CONVERT(datetime, [ordertime])

Answer 3

you want in programatically or in sql server:

in Sql Server:

select orderdate + ordertime as 'YourColumnName'

hope this help.

Answer 4

In MySQL, it'll work like this

SELECT Concat(orderdate, " ", ordertime) FROM vendors ORDER BY vend_name;