If you’re getting error message “Msg 206” that reads “Operand type clash: date is incompatible with int” in SQL Server while trying to add to (or subtract from) a date, it’s probably because you’re trying to perform arithmetic between an integer and a date value.

To fix this issue, either change the date value to a datetime value or use the DATEADD() function.

Example of the Error

Here’s an example of code that produces the error:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT @date + 1;

Result:

Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

In this case, I declared a variable as a date value, assigned a value, then I tried to add an integer to that date.

Solution 1

One way to fix this issue is to use a datetime value instead of the date value:

DECLARE @date datetime;
SET @date = '2035-10-15';
SELECT @date + 1;

Result:

2035-10-16 00:00:00.000

This obviously has the effect of having a (potentially unnecessary) time value included. We can convert the result back to a date value using either CONVERT() or CAST():

DECLARE @date datetime;
SET @date = '2035-10-15';
SELECT CAST(@date + 1 AS date);

Result:

2035-10-16

Solution 2

Another way to fix this issue is to use the DATEADD() function to perform the arithmetic:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT DATEADD(day, 1, @date);

Result:

2035-10-16