How to convert unsigned int accidentally stored as signed int? During a data conversion process, a field that was apparently an unsigned int was stored in a Sql Server int field(which is signed).

To remedy this apparently requires some combination of bitwise operators and type conversion that I'm struggling with.

For example the value 141 is stored as


  -29440 or 0x000000000000000000000000000000000000000000000000000000008D00


and the value 362 is stored as 


  27136 or 0x000000000000000000000000000000000000000000000000000000006A00


Presumably I need to convert to binary first to thwart any implied casting.
A smallint in SQL Server is stored as a signed int16, or 2 bytes, or 16 bits with the 16th bit reserved to indicate the sign (0 = positive, 1 = negative).  

Here are your two examples, converted to the original un-signed int (uint), and int16 values: It looks like in your examples that your bits have been shifted to the left by 8 bits. Noticed the bolded items below are the same.

Original Value 1: uint = 141    = 0x008D = 00000000 10001101 
Was converted to: int16  = -29440 = 0x8D00 = 10001101 00000000 

Original Value 2: uint = 362   = 0x016A = 00000001 01101010
Was converted to: int16  = 27136 = 0x6A00 = 01101010 00000000

Unfortunately, in your second scenario (the conversion from 362 that resulted in 27136), the 1 in the 9th bit position (00000001 01101010) has been dropped during your data conversion. 

It appears your conversion process was something like: 


Shift the original # to the left by 8 bits (this will add 8 bits of 0 padding added on the right-hand side), 
Convert to smallint (16 bit data type), in a way that truncated the 8 left-most bits. 


In this case, then any # greater than 256 in your original dataset would have been converted to a # that represents only the 8 right-most bits with 8 bits of 0 padding adding on the right, and you will not be able to recover any number from your original data that was above 256 because it looks like the left-most 8 bits have been truncated during the failed data conversion to a 16 bit data type.  

For example, given the # 27136 (01101010 00000000) in your current data, you cannot know if this original # was 106 (00000000 01101010) or 362 (00000001 01101010) or 874 (00000011 01101010), or any # that originally had a 1 within the position of bits 9-16.