byte manipulation for int

I've got a column that is as type int(length=4).
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
--
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
SandiyanYou can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
David Portas
SQL Server MVP
--|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000' is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:

>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>|||Thanks Steve. A good catch.
David Portas
SQL Server MVP
--|||Why do you think that this will work? Why does this make an sense to
you?