T-SQL Tutorial

Msg 206 Level 16 - Operand type clash: int is incompatible with date


On Transact SQL language the Msg 206 Level 16 - Operand type clash: int is incompatible with date means that you insert int values into an date column. The date column can be declared DATE, DATETIME, DATETIME2, TIME.


In SQL Server, an operand type clash occurs when a query is attempting to use an operator or function that is not compatible with the data type of the operand. In this case, the error message "operand type clash: int is incompatible with date" is indicating that a value of data type INT (integer) is being used in an operation where a value of data type DATE is expected. This can occur if a column in a table is defined as DATE but an INT value is being used in a query or if a variable or constant of type INT is being used in a query where a DATE value is expected.

To resolve this issue, the INT value must be converted to a DATE value before being used in the query or the query should use a INT value that match the table column definition.

Msg 206 Level 16 Example:

We have the table TEST:

USE model;
GO
CREATE TABLE TEST(
   ID INT NOT NULL PRIMARY KEY,
   NAME VARCHAR(10) NOT NULL,
   BIRTHDAY date );
GO





Invalid insert:

USE model;
GO
INSERT INTO TEST(id, name, birthday) VALUES (1, 'Olivia Wilson',123);
GO

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

Correct insert:

USE model;
GO
INSERT INTO TEST(id, name, birthday)
VALUES (1, 'Olivia','1986-09-14');
GO

Message
(1 row(s) affected)

Other error messages: