OK SQL documentation sucks. I know that, I've known that for years, but it can be helpful and useful at times, so I use it. I thought I remembered correctly what "~" did, but looked it up anyway. I'm reading the usual @#$%, but getting the basic information (While thinking "You have no idea what you are talking about.") then read something flat out wrong.
Is there any place where we can ask Microsoft to fix their documentation some time in the future that won't land in the bit bucket?
What's wrong? Besides all the various statements about it being a signed integer, their binary(2) bit patterns, they said ~170 evaluates to -170. If we used 1's comp machines that would be correct, but all the machines currently manufactured that I know about uses 2's comp and ~170 evaluates to -171. For any signed integer number @n that is not null, this evaluates as true:
if(~@n=-1-@n)...
Also, ~170 will also evaluate as 85 or 1:
declare @i tinyint = 170,@b bit=~170,@bi bigint = 5000000000000
select ~@i,@b,cast(321.345 as BINARY(4)),cast(321 as BINARY(4)),~@bi