Casting and Data Type Conversion

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

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

    Thank you for the question.

  • Very interesting question, thanks.

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Great question, thanks for sharing


  • This was removed by the editor as SPAM

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

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

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

  • 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"?


  • Thanks for the question. I learned something today.

    Everything is awesome!

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


    0000000000000000 -> 0

    1000000000000000 -> -32768

    0111111111111111 -> 32767

    1111111111111111 -> -1's_complement


  • 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

  • Great question, Jason. Thanks!

  • 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
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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

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