# 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 https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

• Great question, thanks for sharing

Thanks

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

Tom

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

BINARY -> DECIMAL

0000000000000000 -> 0

1000000000000000 -> -32768

0111111111111111 -> 32767

1111111111111111 -> -1

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

Fitz

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

• 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
SQL RNNR
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)