Solved

# SQL - Round a dollar value and update it in SQL

Posted on 2005-04-14
2,426 Views
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?

0
Question by:rudyflyer

LVL 25

Assisted Solution

update yourtable set TotalAmount = round(TotalAmount,2)
0

Author Comment

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

LVL 7

Expert Comment

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

LVL 11

Expert Comment

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

LVL 25

Expert Comment

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

LVL 2

Expert Comment

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

LVL 44

Accepted Solution

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

LVL 11

Expert Comment

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

## Featured Post

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…