Operand type clash?

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.

          1. user-defined data types (highest)
          2. sql_variant
          3. xml
          4. datetimeoffset
          5. datetime2
          6. datetime
          7. smalldatetime
          8. date
          9. time
          10. float
          11. real
          12. decimal
          13. money
          14. smallmoney
          15. bigint
          16. int
          17. smallint
          18. tinyint
          19. bit
          20. ntext
          21. text
          22. image
          23. timestamp
          24. uniqueidentifier
          25. nvarchar (including nvarchar(max) )
          26. nchar
          27. varchar (including varchar(max) )
          28. char
          29. varbinary (including varbinary(max) )
          30. 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!