Boolean 'NOT' in T-SQL not working on 'bit' datatype?

Question

Trying to perform a single boolean NOT operation, it appears that under MS SQL Server 2005, the following block does not work

DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = NOT @MyBoolean; SELECT @MyBoolean; 

Instead, I am getting more successful with

DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = 1 - @MyBoolean; SELECT @MyBoolean; 

Yet, this looks a bit a twisted way to express something as simple as a negation.

Am I missing something?

Answer 1

Use the ~ operator:

DECLARE @MyBoolean bit SET @MyBoolean = 0 SET @MyBoolean = ~@MyBoolean SELECT @MyBoolean 

Answer 2

Your solution is a good one... you can also use this syntax to toggle a bit in SQL...

DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = @MyBoolean ^ 1;  SELECT @MyBoolean;