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?

Almagest

possible duplicate of How do I flip a bit in SQL Server? – Roots

Answer 1

Use the ~ operator:

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

Allocution

It's because you're using an int, not a bit. – Allocution

Column is a bit ... could the DB version matter? – Durban

I know that this works in SQL Server 2008. I do this all the time. The question was for SQL Server 2005, which I am not sure if it works there or not. – Pull

Correction: According to MS, this should work in 2005 as well. More info here. – Pull

Nice answer, I just tested it and works fine, even in SQL Server 2000. – Underhand

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;

Henriettehenriha

Just for an FYI, this works because it bitwise exclusive operation. Same as the XOR operator in many languages. This is basically the same as doing SET @MyBoolean = 1 - @MyBoolean except it is using bit math rather than integer math. Even though this is appropriate and works, it can be confusing to people who don't understand bit math. More info here. @Jonas Lincoln's solution is better. – Pull

As an FYI this solution works for calculated fields whereas a case statement does not. Thanks! – Elroyels

Answer 3

Subtracting the value from 1 looks like it'll do the trick, but in terms of expressing intent I think I'd prefer to go with:

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

It's more verbose but I think it's a little easier to understand.

Charette

Answer 4

To assign an inverted bit, you'll need to use the bitwise NOT operator. When using the bitwise NOT operator, '~', you have to make sure your column or variable is declared as a bit.

This won't give you zero:

Select ~1 

This will:

select ~convert(bit, 1)

So will this:

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

Atheist

Answer 5

In SQL 2005 there isn't a real boolean value, the bit value is something else really.

A bit can have three states, 1, 0 and null (because it's data). SQL doesn't automatically convert these to true or false (although, confusingly SQL enterprise manager will)

The best way to think of bit fields in logic is as an integer that's 1 or 0.

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

Ics

Answer 6

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

Mexico

Yes, Bill Gates was so influenced by the BASIC language and the BASIC interpreter he wrote in 1975, that he's never gotten over it and made a real boolean type in SQL Server. I guess the B in BASIC really stands for "Beginner", and that Edsger Dijkstra was right all along! E.g. in standard SQL it is possible to say WHERE BoolField or WHERE NOT BoolField rather than WHERE BitField = 1 – Engenia

Answer 7

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

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

Suction

You missed explaining why -1 would ever arise in the first place. That is: it won't, if the subtraction is expressed in the more logical/intuitive form that the OP used. This is a pointlessly cryptic and round-about way to do it. – Mayfair