Decimals being Dropped by Update

  • 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

  • In the update statement, divide it by 100.00 .. THen you wil get the 2 point precision..

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

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

  • 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