SQL Server 2008: Why won't this round to two decimal places?
12,603
Solution 1
Try changing 16 to 16.0
SELECT
ROUND(WeightInOZ / 16.0, 2)as WeightInLbs
FROM
Inventory
You are seeing strange results because it is treating the results of your division as an integer rather than a decimal. Specifying the .0
tells sql server to treat it as a decimal.
UPDATE:
If the trailing zero's are freaking you out you can always do this:
SELECT
CAST(ROUND(WeightInOZ / 16.0, 2) AS NUMERIC(8,2)) as WeightInLbs
FROM
Inventory
Solution 2
The problem is this:
WeightInOZ / 16
Since you're dealing with two integers, SQL Server truncates the remainder, so by there's no fractional component for it to round.
What you want to do is force it to perform floating-point (or decimal) division. The easiest way would be to change 16
to 16.0
.
SELECT
ROUND(WeightInOZ / 16.0, 2)as WeightInLbs
FROM
Inventory
Author by
William
Updated on June 05, 2022Comments
-
William over 1 year
SELECT ROUND(WeightInOZ / 16, 2)as WeightInLbs FROM Inventory
The result I get looks like an integer 1,2 etc
-
William almost 13 yearsthat will give me this result: 0.060000 which is definitley on rigth track
-
Abe Miessler almost 13 yearsHmm, what result are you expecting?
-
William almost 13 yearsi am expecting 0.06 or 233.23 etc
-
Abe Miessler almost 13 yearsI know it looks weird but those are the same numbers. If you don't like seeing the trailing zeros you can use the method described in my update.
-
William almost 13 yearsOh, I see, that is very clever
-
Andriy M almost 13 years
money
seems to work just as well asnumeric(8,2)
in this case. -
Nicholas Carey almost 13 yearsadding the '.0' makes it a floating point literal, not a decimal literal.
-
Adam Robinson almost 13 years@Nicholas: Is the first part of your comment missing?