Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

  Column name   |   datatype
product_name    | varchar(30)
price           | money
expire_date     | date
expire_time     | time(7)
share|improve this question
23/12/2012 is treated like a math expression (and its overall type is integer). Quote it. – dezso Sep 6 '12 at 9:28
up vote 4 down vote accepted

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:

DECLARE @d DATETIME = '2012-12-23';


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:

DECLARE @d DATE = '23/12/2012';


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:

Bad Habits to Kick : mis-handling date / range queries

And Tibor's excellent article:

The ultimate guide to the datetime datatypes

share|improve this answer
Great answer. Never thought that your answer also include example and their result. Thanks. – Jack Sep 7 '12 at 0:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.