“Operand type clash” with DATE column

Question

I enter the following command into the SQL Server Management Studio

insert into testtable ([product_name], [price], [expire_date], [expire_time]) 
  values ('Teapot', 10.00, 23/12/2012, '12:35:00')

It yields this error:

Operand type clash: int is incompatible with date

These are the columns:

product_name   varchar(30)
price          money
expire_date    date
expire_time    time(7)

23/12/2012 is treated like a math expression (and its overall type is integer). Quote it.

Aaron Bertrand · Accepted Answer · 2012-09-06 12:57:54Z

Please use string delimiters around date literals, and do whatever you can to avoid ambiguous, regional formats like DD/MM/YYYY. How about:

insert into dbo.testtable ([product_name], [price], [expire_date], [expire_time]) 
  values ('Teapot', 10.00, '2012-12-23', '12:35:00');

Luckily, with DATE, YYYY-MM-DD can't be misinterpreted. With DATETIME, it could:

SET LANGUAGE FRENCH;
DECLARE @d DATETIME = '2012-12-23';

Result:

Msg 242, Level 16, State 3
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Your original format could also cause problems even with date:

SET DATEFORMAT MDY;
DECLARE @d DATE = '23/12/2012';

Result:

Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.

Not to sound preachy, but please see this blog post:

And Tibor's excellent article:

Great answer. Never thought that your answer also include example and their result. Thanks.

1 Answer