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?

Solution 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

Solution 2

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

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

Comments

[Kirk Broadhurst]

-1 You can't add date and time datatypes in SQL with a + operator.

[Suresh Chaudhary]

but you can do like convert(varchar(10),orderdate ,101)+''+convert(varchar(10),ordertime) as 'Test'. i just given the approch how you can handle this.

[ZygD]

you wouldn't use strings either. Your results depends on language