SQL SERVER – Adding Datetime and Time Values Using Variables

Datetime datatype stores date and time part. If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.

It is shown below
DECLARE @date DATETIME
SET @date='2010-10-01'
SET @date=@date+'15:00:00'
SELECT @date AS DATETIME

The result is 2010-10-01 15:00:00.000
But it is not possible to add it using a time variable

DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+@time
SELECT @date

The error is

Msg 402, Level 16, State 1, Line 4
The data types datetime and time are incompatible in the add operator.

So the solution is to convert time datatype into datetime and add
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)
SELECT @date AS DATETIME

The result is 2010-10-01 15:00:00.000

So when adding data of datetime and time datatype values, you need to be aware of this.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)