Error in if not exists query in SQL Server

Question

What is wrong with this following query? I can't find the error. Can anyone help me with this issue?

IF (NOT EXISTS(SELECT * 
               FROM chennai_metro_data 
               WHERE TIME1 ='09:00' AND DATE1 ='1-23-2017')) 
BEGIN 
    INSERT INTO chennai_metro_data 
    VALUES (2021700002,'1-23-2017','09:00',1,0,555555) 
END 
ELSE 
BEGIN 
    UPDATE chennai_metro_data 
    SET CUMFLOW = 555555 
    WHERE TIME1 = '09:00' AND DATE1 = '1-23-2017' 
END

I'm getting this error:

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

As a best practice, you should always define the list of columns you're inserting into when using INSERT ..... that helps avoid a lot of problems ! — marc_s
– marc_s, Commented Feb 27, 2017 at 15:10
Also, for string representation of date data types, use either yyyy-mm-dd or yyyymmdd. — Zohar Peled
– Zohar Peled, Commented Feb 27, 2017 at 15:12
@marc_s it works!! Thank you. As per your advice, I have added list of columns. — Lingaraj
– Lingaraj, Commented Feb 27, 2017 at 15:24

marc_s · Accepted Answer · 2017-02-27 15:29:46Z

4

As a best practice, you should always define the list of columns you're inserting into when using INSERT - that helps avoid a lot of problems !

And also: for the dates, to be independent of any language & regional settings, try to use the ISO-8601 format - YYYYMDDD for just dates (no time), or YYYY-MM-DDTHH:MM:SS for date & time.

So try this code:

INSERT INTO chennai_metro_data(col1, col2, ...., colN)
VALUES (2021700002, '20170123', '09:00', 1, 0, 555555)

and replace col1 thorugh colN with your actual column names from that table that you want to insert data into.

answered Feb 27, 2017 at 15:29

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HLGEM Over a year ago

JUst adding, when you have adat mismatch error in an insert without the columns listed, it almost certainly mans the columns in the insert are not in the same order as the columns in the table. This is one reason why it is critical to always add the column list. Also, sometimes people actually change the column order in tables. They shouldn't (because then stuff breaks) but they do.

Collectives™ on Stack Overflow

Error in if not exists query in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related