I am not finding the solution to this issue despite looking specifically for it.
The below query is me trying to get the last decimal places to appear properly.
select CAST(CAST(33 as float)/100 as decimal(5,3))
From here I am looking for how to round properly to .33 or if the result were .339 to round up to .34
What I am finding is that it will only round down to 0 because it is not a whole number.
Can someone point me in the right direction or if there is no good way let me know.
Advertisement
Answer
Make sure your denominator isn’t treated as an integer. You could tack on a .0
or CAST
or CONVERT
it. In the case where you’d expect rounding down to occur:
SELECT 331 / 1000.0; -- gives 0.331000 SELECT ROUND(331 / 1000.0, 2); -- gives 0.330000 SELECT CAST(ROUND(331 / 1000.0, 2) AS DECIMAL(5, 2)); -- gives 0.33
In the case where you’d be expect rounding up to occur:
SELECT 339 / 1000.0; -- gives 0.339000 SELECT ROUND(339 / 1000.0, 2); -- gives 0.340000 SELECT CAST(ROUND(339 / 1000.0, 2) AS DECIMAL(5, 2)); -- gives 0.34
Note: you may not need to do both ROUND
and CAST
since I believe the rounding occurs implicitly, but it makes the intent more clear if you choose to write it as such.