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

Question

Trying to perform one logical NOT operation, it looks like the following block does not work in MS SQL Server 2005

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

Instead, I become more successful with

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

However, it looks a little distorted to express something as simple as negation.

Did I miss something?

Answer 1

Use the ~ operator:

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

Answer 2

Your solution is good ... you can also use this syntax to switch bits in SQL ...

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

Answer 3

Subtracting a value from 1 looks like it's a trick, but in terms of expressing intent, I think I would rather go with:

 SET @MyBoolean = CASE @MyBoolean WHEN 0 THEN 1 ELSE 0 END 

This is more verbose, but I think it is a little easier to understand.

Answer 4

There is no real logical value in SQL 2005; the bit value is something else.

a bit can have three states, 1, 0 and zero (since this is data). SQL does not automatically convert them to true or false (although, vaguely, the SQL enterprise manager will)

The best way to think about bit fields in logic is with an integer equal to 1 or 0.

If you use logic directly in the bit field, it will behave like any other value variable - that is, the logic will be true if it has a value (any value) and false otherwise.

Answer 5

To assign an inverted bit, you need to use the bitwise NOT operator. When using the bitwise NOT operator, '~', you must ensure that your column or variable is declared as a bit.

This will not give you zero:

 Select ~1 

This will:

 select ~convert(bit, 1) 

And so it will be:

 declare @t bit set @t=1 select ~@t 

Answer 6

BIT is a numeric data type, not a boolean. This is why you cannot apply Boolean operators to it.
SQL Server does not have a BOOLEAN data type (not sure about SQL SERVER 2008), so you need to stick with something like @Matt Hamilton's solution.

Answer 7

Use ABS to get the absolute value (-1 becomes 1) ...

 DECLARE @Trend AS BIT SET @Trend = 0 SELECT @Trend, ABS(@Trend-1)