• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2510
  • Last Modified:

SQL - Round a dollar value and update it in SQL


I have a field in SQL Server 2000 titled "TotalAmount" and this is Money(8). I need to change the decimal places to 2 instead of 4.

For example, I have a value of 9.8895 in this field.  I need to write an UPDATE statement that will round this value to 9.89.

How can I do that?

Thanks in advance.
2 Solutions
update yourtable set TotalAmount = round(TotalAmount,2)
rudyflyerAuthor Commented:
If I do that I get trailing zeros on the end.

For example, the value will be 45.0000  or 49.9500.

I need it to be 45.00 or 49.95
The Money datatype has 4 decimal places, so you will always see the two trailing zeros if you keep that datatype.  If you want the column to only have 2 decimal places, you need to change the datatype to something like DECIMAL(16,2).  Before doing that, you need to execute an update statement to round all the values to two decimal places as suggested above.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Changing the datatype and saving will automatically round the values, but no reason to take a chance, run an UPDATE statement first.

I'm not sure if you are looking to store data this way or just return data in this format. If you are looking to return data in this format, then you could use a convert() on select:
  SELECT CONVERT(decimal(16,2),TotalAmount) FROM table1

If updating:
  UPDATE table1
  SET TotalAmount = ROUND(TotalAmount,2)
When you read the data, you can also use cast

select Cast( round(TotalAmount,2) as decimal(16,2) ) as "TotalAmount"
from yourtable

The update statement is still correct, though.

After doing the update, you can:

select Cast(TotalAmount as decimal(16,2) ) as "TotalAmount"
from yourtable
in mysql there exist a truncate() function

mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9

i dnt know if such function exist in MS SQL
there is a VERY IMPORTANT reason why the Money datatype in your table has 4 digits after the decimal point, and if you are truly holding money values in these fields, then you should NOT change the field data type.

The extra decimal digits are carried to properly handle round off when money fields are used in calculations, and when Money values are 'accumulated' (as in a Select SUM(MoneyField) as "Total...).  Without proper 'accounting' for the round off, over time, your calculaons will accumulate significant errors, and ultimately your money values will be wrong.

If you are concerned about DISPLAYING values on forms or reports, then you can handle the rounding when the values are displayed, but you should not change how the values are actually stored in the database.  This woudl be a invitation to possible 'disaster'.

Arthur is correct. Not storing 4 digits to right of decimal to lead to greater problems.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now