SQL Server error Msg 245, Level 16 tells us that there was a problem when trying to convert a value to a specific data type.

You’ll get this error if you try to insert the wrong data type into a column.

To fix this issue, make sure the data type of the value you’re trying to insert, matches the column’s 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', '2020-04-02', 'Dog food');

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2020-04-02' to data type int.

In this case I tried to insert a date into the OrdersId column. We can assume by the error message that this column is an int column, but we should verify that.

If we look at the table definition, we can see the OrderId column’s type:

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

As expected, the OrderId column is an int column.

Solution

To resolve this issue, we need to make sure that we’re inserting the correct value. We also need to ensure that the table definition is appropriate for the data that it needs to store. This will help enforce the data integrity of our database.

In our case, the column’s data type is correct. The problem was caused by accidentally trying to insert the wrong data.

Therefore, to fix the issue, we can change our INSERT statement to insert the correct data.

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

Result:

(1 row affected)

Success!