Rounding Question???

  • I am working on a rounding issue with a MSQL 2000 database. The problem is I have a few thousands records that get processed through some stored procedures and after the calculation process we end up with partial cents. We then have to transfer our data to an accounting system and it of course only recognizing cents so it rounds the data to two decimal places. This is where the problem lies. Ideally I would like to round before we transfer the data, but I need to solve the problem of it rounding off a few cents. Do you know of any rounding procedures that may fix this? If you have any ideas or can suggest some good resources to look at besides the basic ones I would appreciate it. thanks


  • One way is to convert to a string, then truncate the extra decimal places you don't need.


  • not sure whether this is what you need but , for rounding off amounts in my systems i keep a field which stores how many decimals to round of to

    For e.g round of to next 10 cents

    which means that an amount of 1000.238 would be rounded off to 1000.30

    Hope This Helps

  • If you use the decimal() data type and specify (for example) decimal(16,2), all your amounts will be stored in cents all the time. Round will occur in the calculations but be stored in cents. If you need higher accuracy, you can use decimal(18,4) in your data base and caluclations, then round when you select the data.

    for example

    select myAmount = cast(myAmount as decimal(16,2))

    from ....

    and use this as your input to your application.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply