This question came up today.

in c# i have a:

a = !a

(if false makes it true, if true makes it false)

in sql i want to do the same with a BIT variable

In a language like C# you can use ! for a bool to make it become true if it was false and vice versa

So if you run this

```
bool a = true;
a = !a;
Console.WriteLine(a.ToString());
a = !a;
Console.WriteLine(a.ToString());
Console.ReadLine();
```

The output will be

False

True

How can you do this in SQL Server? It is pretty easy, take a look

```
select ~ CONVERT(bit,0)
select ~ CONVERT(bit,1)
```

The ~ symbol is the Bitwise NOT operator, here is what books on line has to say about the Bitwise NOT operator.

The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.

Let’s take a look at another example. What do you think will happen here?

```
select ~ CONVERT(tinyint,0)
select ~ CONVERT(tinyint,1)
```

That actually returns

255

254

Why is that? Here is a simple explanation

With a tinyint possible values are between 0 and 255, when you have 0 all bits are turned off, when you flip it, you turn all bits on and you get 255

00000000 = 0 ( 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0) 11111111 = 255 (1 + 2 + 4 + 8 + 16 + 32 + 64 + 128)

When you have 1 all bits are turned off except for the first bit, when you flip it you turn all bits on except for the first bit

00000001 = 1 ( 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0) 11111110 = 254 (0 + 2 + 4 + 8 + 16 + 32 + 64 + 128)

If you want to use tinyint or int you can use the following code

```
select CONVERT(tinyint,0) ^ 1 as [tinyint], 0^1 as [int]
select CONVERT(tinyint,1) ^ 1 as [tinyint], 1^1 as [int]
```

The ^ operator is the Bitwise Exclusive OR operator. Here is what books on line has to say about Bitwise Exclusive OR

The ^ bitwise operator performs a bitwise logical exclusive OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either (but not both) bits (for the current bit being resolved) in the input expressions have a value of 1. If both bits are 0 or both bits are 1, the bit in the result is cleared to a value of 0.

Let’s take a closer look

*The bits in the result are set to 1 if either (but not both) bits (for the current bit being resolved) in the input expressions have a value of 1.*

**0 ^ 1**

00000000

00000001

——–

00000001

*If both bits are 0 or both bits are 1, the bit in the result is cleared to a value of 0.*

**1^1**

00000001

00000001

——–

00000000

So in the end if you want to flip a bit use the Bitwise NOT operator

```
select ~ CONVERT(bit,0)
select ~ CONVERT(bit,1)
```

*** **Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum**