CASTING Binary to BIGINT

Question

CASTING Binary to BIGINT

Thomas Franz

Hall of Fame

Points: 3893
More actions
September 24, 2018 at 11:35 pm

#373220

Comments posted to this topic are about the item CASTING Binary to BIGINT
God is real, unless declared integer.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72624 More actions · Answer 1

Nice question, thanks Thomas
very detailed explanation too

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 2

Amazing!

I didn't realize it was October 12 already 🙂

Just because you're right doesn't mean everybody else is wrong.

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 3

Rune Bivrin - Tuesday, September 25, 2018 1:54 AM
I didn't realize it was October 12 already 🙂

well, time passes faster, the older you get 🙂 Be aware - maybe next week is already XMas ...

God is real, unless declared integer.

Stewart "Arturius" Campbell SSC Guru Points: 72624 More actions · Answer 4

t.franz - Tuesday, September 25, 2018 2:17 AM
Rune Bivrin - Tuesday, September 25, 2018 1:54 AM
I didn't realize it was October 12 already 🙂
well, time passes faster, the older you get 🙂 Be aware - maybe next week is already XMas ...

someone used DBCC TIMEWARP again, and forgot to turn it off....

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 5

very interesting behaviour
thanks

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 6

Shayn Thomas - Tuesday, September 25, 2018 6:51 AM
very interesting behaviour
thanks

Another "undocumented feature" from MS.

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 7

Kaye Cahs - Tuesday, September 25, 2018 7:05 AM
Shayn Thomas - Tuesday, September 25, 2018 6:51 AM
very interesting behaviour
thanks
Another "undocumented feature" from MS.

I meant the " DBCC TIMEWARP " feature, not the casting of binary to integers.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Kaye Cahs - Tuesday, September 25, 2018 7:28 AM
Kaye Cahs - Tuesday, September 25, 2018 7:05 AM
Shayn Thomas - Tuesday, September 25, 2018 6:51 AM
very interesting behaviour
thanks
Another "undocumented feature" from MS.
I meant the " DBCC TIMEWARP " feature, not the casting of binary to integers.

The history of DBCC TIMEWARP is hidden deep in the bowels of this thread: The Thread

webrunner SSC-Dedicated Points: 31862 More actions · Answer 9

Rune Bivrin - Tuesday, September 25, 2018 1:54 AM
Amazing!
I didn't realize it was October 12 already 🙂

Same here! When I saw the date I feel like I got a taste of time travel.

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

HappyGeek SSCoach Points: 18729 More actions · Answer 10

Good question to end next week on 😀, thanks Thomas.

...

Ray Herring SSCertifiable Points: 5533 More actions · Answer 11

Nice question but I am pretty sure the technical explanation is flawed.
Generally machines represent signed integers in 2's complement not sign-magnitude. Your explanation basically implies sign-magnitude (i.e. "... by turning the first (leftmost) bit on.")
At first glance, there is very little difference but sign-magnitude has the unfortunate characteristic of containing two zeros (+0 and -0).
2's complement solves that problem at the cost of losing a bit of symmetry. For example, that's why 16-bit integer range is -32768 to +32767.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 12

Ray Herring - Friday, October 12, 2018 7:59 AM
Nice question but I am pretty sure the technical explanation is flawed.
Generally machines represent signed integers in 2's complement not sign-magnitude. Your explanation basically implies sign-magnitude (i.e. "... by turning the first (leftmost) bit on.")
At first glance, there is very little difference but sign-magnitude has the unfortunate characteristic of containing two zeros (+0 and -0).
2's complement solves that problem at the cost of losing a bit of symmetry. For example, that's why 16-bit integer range is -32768 to +32767.

Ray, you are correct regarding that the numbers are stored as Two's Compliment (Wikipedia comparison of Signed Number Representations). For verification, the following query shows several translations that match the values found in the comparison chart on that Wikipedia page:

SELECT CONVERT(BINARY(4), -0) AS [-0], -- 0x00000000 CONVERT(BINARY(4), -1) AS [-1], -- 0xFFFFFFFF CONVERT(BINARY(4), -2) AS [-2], -- 0xFFFFFFFE CONVERT(BINARY(4), -128) AS [-128]; -- 0xFFFFFF80

Though to be fair to t.franz, the Base-2 representations in the explanation are correct. It is only the wording that is incorrect. The explanation from that Wikipedia page is:

Negating a number (whether negative or positive) is done by inverting all the bits and then adding one to that result.

The net-effect of "inverting all the bits" is, of course, that the left-most will be "on". That is the similarity that Ray is referring to. But other bits change as well.

To easily see the Base-2 representations, I use the Convert_Base10toBase2 function, which is available in the Free version of SQL# SQLCLR library (which I wrote). For example:

SELECT SQL#.Convert_Base10toBase2(-1) AS [-1], SQL#.Convert_Base10toBase2(-2) AS [-2], SQL#.Convert_Base10toBase2(-1234) AS [-1234];

Returns:

-1 = 1111111111111111111111111111111111111111111111111111111111111111 -2 = 1111111111111111111111111111111111111111111111111111111111111110 -1234 = 1111111111111111111111111111111111111111111111111111101100101110

There is also the complimentary function, Convert_Base2toBase10, to convert in the other direction:

SELECT SQL#.Convert_Base2toBase10(N'1111111111111111111111111111111111111111111111111111111111111111') AS [-1], SQL#.Convert_Base2toBase10(N'1111111111111111111111111111111111111111111111111111111111111110') AS [-2], SQL#.Convert_Base2toBase10(N'1111111111111111111111111111111111111111111111111111101100101110') AS [-1234];

Returns:

-1 -2 -1234 ---- ---- ------- -1 -2 -1234

Take care, Solomon...

P.S. The SQL# library also has functions for splitting Base-2 and BitMasked BIGINTs into the individual bits that make up the passed-in value. For example:

SELECT SQL#.Convert_Base10ToBase2(9289) AS [9289];

Returns:
0000000000000000000000000000000000000000000000000010010001001001

Using those 2 original values, we can see which bits are "on", in terms of both the position and the value:

SELECT * FROM SQL#.Util_UnBitMask(9289);

SELECT * FROM SQL#.Util_GetBase2Bits(0000000000000000000000000000000000000000000000000010010001001001);

Both of those TVFs return the following:

BitNum BitVal 1 1 4 8 7 64 11 1024 14 8192
However, these two TVFs are only available in the Full (i.e. paid-for) version.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR