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
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
16 People are following this question.
