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?

possible duplicate of [How do I flip a bit in SQL Server?](http://stackoverflow.com/questions/171173/how-do-i-flip-a-bit-in-sql-server) — Guillermo Gutiérrez, May 24 '14 at 06:14

score 153 · Accepted Answer · answered Oct 07 '08 at 10:37

153

Use the ~ operator:

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

answered Oct 07 '08 at 10:37

Jonas Lincoln

9,157
9
33
47

11

It's because you're using an int, not a bit. – Jonas Lincoln Aug 05 '10 at 14:07
4

Column is a bit ... could the DB version matter? – Martin Aug 10 '10 at 12:53
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. – Dan VanWinkle Dec 14 '11 at 19:36
3

Correction: According to MS, this should work in 2005 as well. More info [here](http://msdn.microsoft.com/en-us/library/ms173468(v=SQL.90).aspx). – Dan VanWinkle Dec 14 '11 at 19:49
3

Nice answer, I just tested it and works fine, even in SQL Server 2000. – Alberto Martinez Apr 09 '12 at 14:04

score 25 · Answer 2 · answered Oct 07 '08 at 09:39

25

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;

answered Oct 07 '08 at 09:39

Galwegian

40,271
15
106
157

2

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](http://msdn.microsoft.com/en-us/library/ms176122.aspx). @Jonas Lincoln's [solution](http://stackoverflow.com/questions/177762/boolean-not-in-t-sql-not-working-on-bit-datatype#answer-177893) is better. – Dan VanWinkle Dec 14 '11 at 19:46
1

As an FYI this solution works for calculated fields whereas a case statement does not. Thanks! – anyeone Aug 19 '13 at 21:23

score 22 · Answer 3 · answered Oct 07 '08 at 09:42

22

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.

answered Oct 07 '08 at 09:42

Matt Hamilton

188,161
60
377
317

FistOfFury · Answer 4 · 2016-08-22T19:24:47.893

11

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

edited Aug 22 '16 at 19:24

answered Apr 24 '12 at 14:24

FistOfFury

5,598
5
43
55

score 10 · Answer 5 · answered Oct 07 '08 at 10:54

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.

aku · Answer 6 · 2008-10-07T10:04:32.240

5

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.

edited Oct 07 '08 at 10:04

answered Oct 07 '08 at 09:42

aku

115,356
31
164
200

Yes, Bill Gates was so influenced by the BASIC language and the [BASIC interpreter](https://en.wikipedia.org/wiki/Altair_BASIC) 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](https://en.wikipedia.org/wiki/BASIC) really stands for "Beginner", and that Edsger Dijkstra was [right all along](https://www.cs.virginia.edu/~evans/cs655/readings/ewd498.html#:~:text=It%20is%20practically%20impossible,regeneration)! E.g. in standard SQL it is possible to say `WHERE BoolField` or `WHERE NOT BoolField` rather than `WHERE BitField = 1` – Reversed Engineer Mar 31 '21 at 11:53

score 4 · Answer 7 · edited Oct 06 '11 at 12:39

4

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

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

edited Oct 06 '11 at 12:39

Jason Plank

2,322
4
29
39

answered Aug 24 '10 at 16:46

Stephen B Craver

41
1

2

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. – underscore_d Sep 18 '17 at 12:31

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

7 Answers7

Linked

Related