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
Share:
12,603
William
Author by

William

Updated on June 05, 2022

Comments

  • William
    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
    William almost 13 years
    that will give me this result: 0.060000 which is definitley on rigth track
  • Abe Miessler
    Abe Miessler almost 13 years
    Hmm, what result are you expecting?
  • William
    William almost 13 years
    i am expecting 0.06 or 233.23 etc
  • Abe Miessler
    Abe Miessler almost 13 years
    I 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
    William almost 13 years
    Oh, I see, that is very clever
  • Andriy M
    Andriy M almost 13 years
    money seems to work just as well as numeric(8,2) in this case.
  • Nicholas Carey
    Nicholas Carey almost 13 years
    adding the '.0' makes it a floating point literal, not a decimal literal.
  • Adam Robinson
    Adam Robinson almost 13 years
    @Nicholas: Is the first part of your comment missing?