CODEWITHSUNDEEP
sql-servert-sqlroundingcurrency
Redtopia
Redtopia

Reputation: 5237

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).

Upvotes: 1

Views: 1380

Answers (2)

Tom Robinson
Tom Robinson

Reputation: 1910

You have mis-read your cited article. To round to the nearest hundredth, you look only at the digit in the thousandth position, where in the case of 123.4545 is "4", so you would round down. The SQL Server ROUND function does what you want.

round(round(amount,3),2) produces a nonsensical result, rounding 123.4500 thru 123.4544 to 123.45 and rounding 123.4545 thru 123.4599 to 123.46

Upvotes: 0

Kostya
Kostya

Reputation: 1605

this works

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

Upvotes: 2

Related Questions