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,
Sandiyan

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?

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 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:
Show quoteHide quote

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:

Show quoteHide quote

Thanks Steve. A good catch.

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:

Show quoteHide quote

Thanks Steve. A good catch.

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:
Show quoteHide quote

Why do you think that this will work?  Why does this make an sense to
you?

Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Query Help
FIFO report
Performance Issues using Aggregate MAX function within a temp table