question

LarryWhiteEagle avatar image
LarryWhiteEagle asked

How do I take calced Money field to next whole $1,000?

What is the easiest way to take a SQL Money field that has been calculated from a variety of variables to the next whole 1,000 dollars ... as in not Rounding to nearest 1,000 but taking for example $33,125.10 to $34,000 ? Thanks, Larry
roundmath
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
Add 499.99 to it (enough to round anything below 500 up to the next 1000, but not enough to round an even 1,000 up) then use round(value, -3) which rounds out the 100s DECLARE @BOB table(amount money) INSERT @bob VALUES(1000.00), (1000.01), (103.61),( 999.74),( 1001.19),( 1300.01),( 1874.23),(33125.10) SELECT Amount, ROUND(amount + 499.99, -3) AS RoundedAmount FROM @bob You should see values like this: Amount RoundedAmount --------------------- --------------------------------------- 1000.00 1000.0000 1000.01 2000.0000 103.61 1000.0000 999.74 1000.0000 1001.19 2000.0000 1300.01 2000.0000 1874.23 2000.0000 33125.10 34000.0000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.