CASTING Binary to BIGINT

  • Comments posted to this topic are about the item CASTING Binary to BIGINT

    God is real, unless declared integer.

  • 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”

  • Amazing!

    I didn't realize it was October 12 already πŸ™‚


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

  • 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.

  • 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”

  • very interesting behaviour
    thanks

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

  • Shayn Thomas - Tuesday, September 25, 2018 6:51 AM

    very interesting behaviour
    thanks

    Another "undocumented feature" from MS.

  • 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.

  • 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

  • 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

  • Good question to end next week on πŸ˜€, thanks Thomas.

    ...

  • 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 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

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

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