KyloRen KyloRen - 4 months ago 22
SQL Question

Concatenate time value to match row's Date SQL Server

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

Datetime
column.
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)
ELSE NULL
END
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
Date_Data
(data type is
DATE
) and concatenate it with just a time value into a column named
Time_Data_1
(data type is
DATETIME
)

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

Type should be same while adding time, try

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