Post reply

Casting and Data Type Conversion

  • July 14, 2014 at 8:44 pm

    #293289

    Comments posted to this topic are about the item Casting and Data Type Conversion

  • July 15, 2014 at 12:45 am

    #1729327

    Nice to know, I would have expected to return an overflow.

    Thank you for the question.

  • July 15, 2014 at 1:21 am

    #1729337

    Very interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 15, 2014 at 1:27 am

    #1729338

    Great question, thanks for sharing

    Thanks

  • July 15, 2014 at 3:22 am

    This was removed by the editor as SPAM

  • July 15, 2014 at 5:41 am

    #1729410

    Nice question to get the brain moving in the morning. Thanks.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • July 15, 2014 at 6:26 am

    #1729414

    thank you for the post, interesting one.

    this is all due to the "negative" sign... remove that sign, all we get is 2, as the tinyint stores only 0 to 255 (positive numbers) the actual varbinary which is holding 2 with the sign "0xFFFFFFFE".... something happens in sql and it gives 254 as output.

    (I guess.. this is the best stupid explanation from side.... 😀 😛 )

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • July 15, 2014 at 6:43 am

    #1729420

    Wowwwww! I need to read up on my varbinary...

  • July 15, 2014 at 6:51 am

    #1729426

    Nice question.

    A very odd statement in the explanation though: "Tinyint is only 2 bytes". No it isn't, it's only 1 byte. Perhaps it meant to say "only 2 quartets"?

    Tom

  • July 15, 2014 at 7:22 am

    #1729440

    Thanks for the question. I learned something today.


    Everything is awesome!

  • July 15, 2014 at 7:23 am

    #1729441

    This is two's complement numbers being used.

    When converting down from varbinary to an integer data type you only get the last (least significant) byte. In this case the least significant bits are 1111 1110.

    In tinyint with would equate to the decimal values : 128,64,32,16,8,4,2 => 254

    With the signed SQL integer types then the most significant value is a negative of what you would expect. So for a smallint (2 bytes) the values are : -32768, 16384,8192,4096,2048,1024,512,256,128,64,32,16,8,4,2,1. In this format :

    BINARY -> DECIMAL

    0000000000000000 -> 0

    1000000000000000 -> -32768

    0111111111111111 -> 32767

    1111111111111111 -> -1

    http://en.wikipedia.org/wiki/Two's_complement

    Fitz

  • July 15, 2014 at 7:23 am

    #1729442

    Love Love Love the question. When casting and converting, I think we sometimes focus more on what data types can be converted to which other types, and don't think about how the length of the datatype in storage will affect the actual value stored. This question points it out very well. Bravo!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • July 15, 2014 at 8:03 am

    #1729467

    Great question, Jason. Thanks!

  • July 15, 2014 at 8:22 am

    #1729482

    Interesting question.

    It seems this would not be an issue if tinyint supported signed values.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 15, 2014 at 8:30 am

    #1729488

    This is definitely off the beaten path. Thanks, Jason!

Viewing 15 posts - 1 through 15 (of 23 total)

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