Now this is something that I’ve seen asked a few times and it’s always a question that gets a fair number of different answers.  How do we go about converting a positive number to a negative, or the other way around?

I’ll give you my solution and it uses a little bit of bitwise logic.

The Short Answer

The short answer to the question is to simply apply a bitwise NOT to the number that you want to convert and add 1 to the result.  I wrote about bitwise logic in The Arcane Science of Bitwise Logic and SQL Server but didn’t mention the bitwise NOT.

If you want to apply a bitwise NOT to a value, simply use the ~

Give it a go, try converting 10 to a negative,

SELECT ~ 10 + 1

The result, -10.

You can also go the other way and convert a negative to a positive, give it a go with -9

SELECT ~ -9 + 1

…and the result, 9.

So that’s the short answer and that’s how to convert a positive to a negative and vice versa.

Carry on reading if you want to know why this works and exactly what’s happening.

The Long Answer

To really understand what’s going on, we need to understand what a biwise NOT actually does.

In nerdy talk, a bitwise NOT will return the ones’ complement of the value.  What that means to you and me is that all the bits in a particular value are inverted, a 1 will become a 0 and a 0 will become a 1.

Let’s take a look at the value 124, in binary that’s represented as 01111100.  Have a look at see what happens when we apply a bitwise NOT to that value.

With the bits inverted, we get the number 131.

So how does this help us when we want to convert a positive number to a negative?

First of all you might want to remind yourself how binary works, I explained binary in Secret Codes And SQL Server, Part 1: Writing Our Own Encryption Algorithm and Cracking it.

Lets look at a few SQL datatypes for a moment,

Do you notice anything with those numbers?

Well, let’s take an int for example.  The max value of an int in SQL is 2,147,483,648 but it’s stored as a 4 byte datatype and the biggest number that we can represent in 4 bytes is 4,294,967,295.  What’s going on here?

Well all integer datatypes in SQL (except for tinyint) are signed datatypes, that’s what lets us represent both positive and negative numbers.  Because a value can be either positive or negative we need some way to tell them apart.

In binary, if we want to represent a negative number we use the twos’ complement value.  Twos’ complement simply means that we take the number, invert the bits and then add 1.

For example if we have a 1 byte signed datatype,

00000001 = 1

invert the bits,

11111110

add one

11111111 = -1

01000100 = 68

invert the bits (apply a ~)

10111011

add one

10111100 = -68

In this notation, the most significant bit, the one over on the left had side represents the signing of the value.  If that bit’s a 0 then we know that it’s going to be a positive number, but if it’s a 1 then the number will be negative.

So, going back to the original question, by applying the bitwise NOT (~) we’re flipping the bits and then adding one which will convert a positive to a negative or a negative to a positive.