KyloRen KyloRen - 1 year ago 62
SQL Question

Concatenate time value to match row's Date SQL Server

I have a SQL statement that compares just the times in a

If the criteria is met then I need the the column to update and concatenated a time value to the corresponding date in that row.

Here is the SQL statement,

declare @first datetime
set @first = '2016/10/10 06:47:16.123'
declare @second datetime
set @second = '2016/10/10 10:47:16.123'
declare @myTime datetime
set @myTime = '2016/10/10 00:15:16.123'

UPDATE Staff_Manager.dbo.Staff_Time_TBL
SET Time_Data_1 = CASE
WHEN CAST(Time_Data_1 AS time)
BETWEEN CAST(@first AS time) AND CAST(@second AS time)
THEN CAST(Date_Data AS date) + CAST(@myTime AS time)
WHERE Staff_No = 1903 AND Date_Data BETWEEN '2016/2/1' AND '2016/3/1'

After the criteria is met I need this line below to get the date from that row, from a column named
(data type is
) and concatenate it with just a time value into a column named
(data type is

THEN CAST(Date_Data AS date) + CAST(@myTime AS time)

Presently I am getting an error,

Operand data type date is invalid for add operator.

I believe this was able to be done before 2008 , but I am on SQL server 2014 ,How do you go about doing something like this.

Answer Source

Type should be same while adding time, try

cast(cast(Date_Data as date) as datetime) + cast(CAST(@myTime AS time) as datetime)