concatenate two columns with date and time in sql?
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 datetim开发者_如何转开发e values(03/02/2011 10:34:16.190)
....
any suggestion?
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
Assuming you are working with a DATE
and a TIME
, you need to convert before you can add.
SELECT [orderdate] + CONVERT(datetime, [ordertime])
you want in programatically or in sql server:
in Sql Server:
select orderdate + ordertime as 'YourColumnName'
hope this help.
In MySQL, it'll work like this
SELECT Concat(orderdate, " ", ordertime) FROM vendors ORDER BY vend_name;
精彩评论