SQL Server error Msg 206, Level 16 is a common error to get when inserting data into a table.

It happens when you’re trying to insert data into a column that is incompatible with the data type you’re trying to insert.

This could happen if you accidentally try to insert data into the wrong column (or even the wrong table). But it could also happen if you incorrectly assume that SQL Server will convert the data for you.

To fix this issue, make sure you’re inserting the correct data type.

Example of Problem Code

Here’s an example of code that results in this error.

INSERT INTO Orders(OrderId, OrderDate, OrderDesc) 
VALUES ('2020-04-02', 1, 'Dog food');

Result:

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

In this case, it’s quite obvious where I’m going wrong, just by looking at the column names and the values I’m trying to insert.

I’m trying to insert values in the wrong order.

The problem is that I’m trying to insert an integer into a date column. Specifically, I’m trying to insert the value 1 into a column called OrderDate.

Of course, it’s not the column name that’s causing the problem. It’s the data type that I’m trying to insert into it.

Here’s the code I used to create the table:

CREATE TABLE Orders (
    OrderId int NOT NULL,
    OrderDate date NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrders PRIMARY KEY CLUSTERED(OrderId, OrderDate)
    );

We can see by the table definition that the OrderDate column uses a date data type.

Blessing in Disguise?

As frustrating as it can be to get an error, sometimes it could be the best thing that ever happened to you.

If the code doesn’t result in an error, we might accidentally insert the wrong data into the database. This would diminish the data integrity of our database.

Imagine that the OrderDate column is a datetime instead of date:

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderId int NOT NULL,
    OrderDate datetime NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrders PRIMARY KEY CLUSTERED(OrderId, OrderDate)
    );

Now imagine we try to insert the following data into that table:

INSERT INTO Orders(OrderId, OrderDate, OrderDesc) 
VALUES (1, 1, 'Dog food');

Result:

(1 row affected)

Huh? No error?

Exactly. No error. This is because the datetime type is compatible with the int type. In other words, SQL Server took our int value and converted it into a datetime value.

Here’s what our table ends up looking like after that insert operation.

SELECT * FROM Orders;

Result:

+-----------+-------------------------+-------------+
| OrderId   | OrderDate               | OrderDesc   |
|-----------+-------------------------+-------------|
| 1         | 1900-01-02 00:00:00.000 | Dog food    |
+-----------+-------------------------+-------------+