Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)
share|improve this question
4  
23/12/2012 is treated like a math expression (and its overall type is integer). Quote it. –  dezso Sep 6 '12 at 9:28

1 Answer 1

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:

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:

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

 
discard

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.