April 28, 2010 at 3:45 am
Hello all,
The Update statement below is dropping decimals after a divide by 100. Is there any way that I can prevent this?
I realise that the data type for one of the columns is bigint, rather than real, and I suspect this is contributing. However I am unfortunately not able to change the data type.
Can anyone help with this please?
Create Table #Test(
Amount_Sterling real,
Amount real,
local_amount bigint)
Insert into #test (amount, local_amount)
select 15.5,NULL
union all select 143.59,8500
union all select 1065,NULL
union all select 419.97,36522
union all select 55,NULL
union all select 385.16,22800
union all select 173.6,NULL
union all select 148.68,13005
union all select 115,NULL
union all select 70.95,4250
union all select 360,NULL
union all select 92.98,NULL
union all select 5.14,NULL
union all select 578.99,6800
union all select 20,NULL
union all select 374.93,16000
union all select 285.24,3350
union all select 30,NULL
union all select 49.5,NULL
union all select 584.4,6050
union all select 80,NULL
union all select 300,NULL
union all select 253.4,14999
union all select 10,NULL
union all select 805.37,70500
union all select 6.94,NULL
union all select 25,NULL
union all select 785.21,46050
union all select 25,NULL
union all select 352.5,NULL
update #test
Set Amount_Sterling =
Case when local_amount is null then amount
else local_amount/100
end
select * from #test
drop table #test
April 28, 2010 at 3:55 am
In the update statement, divide it by 100.00 .. THen you wil get the 2 point precision..
April 28, 2010 at 4:03 am
Or further, to be fool proof, modify your UPDATE statment like this
update #test
Set Amount_Sterling =
Case when local_amount is null then cast(amount as real)
else cast(local_amount as real) / 100.00
end
Type-Cast the data to REAL and do the UPDATE.
Hope this helps you 🙂
April 28, 2010 at 4:08 am
Both methods work perfectly - thank you!
I will use the one that you label as foolproof, to be safe.
Many thanks for your kind assistance.
April 28, 2010 at 4:11 am
My pleasure, Harvey 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply