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

SQL - Round a dollar value and update it in SQL

Hello,

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.
0
rudyflyer
Asked:
rudyflyer
2 Solutions
 
jrb1Commented:
update yourtable set TotalAmount = round(TotalAmount,2)
0
 
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
0
 
Lori99Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jokra_the_BarbarianCommented:
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)
0
 
jrb1Commented:
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
0
 
Marvin_CarredoCommented:
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
0
 
Arthur_WoodCommented:
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'.

AW
0
 
Jokra_the_BarbarianCommented:
Arthur is correct. Not storing 4 digits to right of decimal to lead to greater problems.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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