Search code examples
sql-servert-sqlroundingcurrency

tsql - update money field to round to nearest hundredth (2 decimal places)

What would be a query to update a money field to the hundredth. I've got some values that go out to the thousandth because I didn't round them going into the database. Now I need to fix that.

I was thinking along the lines of:

UPDATE Transactions
SET amount = ROUND(amount, 2)

But that doesn't work because according to the documentation, ROUND(123.4545, 2) results in 123.45, and in the case of money, it should be 123.46 (if it is 5 or above, we give it a shove).

Solution

  • this works

    UPDATE Transactions
SET amount = round(round(round(amount,4),3),2)