Casting and Data Type Conversion

  • Jason Whitish

    SSChasing Mays

    Points: 642

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

  • Iulian -207023

    SSCertifiable

    Points: 7509

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

    Thank you for the question.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Great question, thanks for sharing

    Thanks

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286983

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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • Mike Hays

    SSCommitted

    Points: 1871

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

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks for the question. I learned something today.



    Everything is awesome!

  • Mark Fitzgerald-331224

    SSCrazy Eights

    Points: 8489

    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

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

  • stephen.long.1

    SSCrazy

    Points: 2577

    Great question, Jason. Thanks!

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Revenant

    SSC-Forever

    Points: 42467

    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