From time to time you might come across this error.
Msg 206, Level 16, State 2, Line 13
Operand type clash: int is incompatible with date
But what causes it? It is similar to a type mismatch error in the fact that there is a comparison of two data points that are of different data types they are not compatible.
Let’s recreate this error. The code below will create a temp table and then insert a row. The final step will read from the table. Notice that the HireDate column is a Date data type while the comparison in the WHERE clause is comparing the Hiredate column to in TINYINT data type.
–Create Table
CREATE TABLE #Test (
ID INT,
HireDate DATE,
)
–Insert a row
INSERT INTO #Test(ID,HireDate)
VALUES (102,6-6-2005)
–Read the data and recreate the error
SELECT *
FROM #Test
WHERE HireDate = 14
As you may know, SQL Server will attempt to automatically convert the data to a similar data type using the data type precedence list. From Microsoft, below is the order of the precedence. For example, if SQL Server sees a comparison of a VARCHAR and an INT data type, SQL Server will attempt to convert the VARCHAR to an INT data type because INT is higher in the list.
-
-
-
-
- user-defined data types (highest)
- sql_variant
- xml
- datetimeoffset
- datetime2
- datetime
- smalldatetime
- date
- time
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar (including nvarchar(max) )
- nchar
- varchar (including varchar(max) )
- char
- varbinary (including varbinary(max) )
- binary (lowest)
-
-
-
In our example, SQL Server is attempting to convert the TINYINT value of 14 to a DATE data type. Of course, this isn’t possible and the error will be thrown.
Now we know what can cause this error, what do we do to fix it? The solution is to address the data type issue. This can be done a number of ways, including using the CONVERT\CAST functions or even changing the data type in the tables.
This error can not only happen in SELECT statements, it can will happen on other DML statements as well.
If you run this statement against the above temp table, you will get the same error.
— Insert a new row and receive the error
INSERT INTO #Test(ID,HireDate)
VALUES (102,13)
Hopefully this helps you out a bit and thanks for visiting my blog!