Except operation in TSQL returning error

Question

I need to apply an EXCEPT (same as MINUS in Oracle) operation in TSQL. I tried with a simple code

select * from Table1
except
select * from Table1 where calndr_dt between '2014-10-01' and '2014-10-10'`

Each sub query is executing fine. But when joined with EXCEPT, it is returning the following error message.

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 2, column: 1: Incorrect syntax near 'except'.

I assume your actual query is more complex because surely this could be done by just running the second query and bracketing the WHERE clause with NOT ()? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 10, 2014 at 7:37
It doesn't say "Incorrect syntax near the keyword 'except'."? What version are you on? If <2005 it is not available. — Martin Smith
– Martin Smith, Commented Oct 10, 2014 at 7:44
Yes the actual query is much more complex. There also I got the same error. That is why I thought of experimenting with a simple piece of code. — Thomas Cherian
– Thomas Cherian, Commented Oct 10, 2014 at 8:51
It works on SQLFiddle, so I'm not sure where the error could be? Could you post the whole code? sqlfiddle.com/#!6/dcf8f/1 — NickyvV
– NickyvV, Commented Oct 10, 2014 at 9:18

Razvan Socol · Accepted Answer · 2014-10-11 18:00:30Z

0

Remove the ORDER BY clause from the first query, if you have this clause.

answered Oct 11, 2014 at 18:00

Razvan Socol

5,7592 gold badges22 silver badges35 bronze badges

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

1 Comment

No. I am not using any ORDER BY clause.