2

I have a report field that can contain different types of data, therefore I need to do my rounding and formatting using functions.

In one case, I have a decimal(9,2) value storing hours that I need to display as minutes with zero decimal places.

Round(foo*60, 0), where foo = .01 (which is 0.6 when multiplied by 60), is outputting 1 like I would expect.

Round(foo*60, 0), where foo = .21 (which is 12.6 when multiplied by 60), is outputting 12 instead of 13.

Why? How can I get it to consistently use standard 4/5 rounding?

dusan
  • 9,104
  • 3
  • 35
  • 55
Mark Freeman
  • 1,155
  • 3
  • 24
  • 42

2 Answers2

0

I can't reproduce your results. Perhaps you are hiding some other relevant details.

Declare @foo Decimal(9,2)
Set @foo = 0.21

Select Round(@Foo * 60, 0), @foo * 60

The output from the above code is 13.00 and 12.60 respectively.

Is it possible that the Foo column is a float, or that there is more precision in the foo column that you expect? For example, the following code produces 12 (instead of 13).

Declare @foo float
Set @foo = 0.208

Select Round(@Foo * 60, 0), @foo * 60, Round(@foo, 2)
George Mastros
  • 24,112
  • 4
  • 51
  • 59
  • 1
    Foo is a DECIMAL(9,2) in the database. The issue is not the ROUND function in SQL Server, but the Round function in SSRS 2005 (using the Visual Studio 2008 flavor of BIDS). It is quite possible that they behave differently. – Mark Freeman Mar 10 '11 at 18:55
0

Take a look at this other stackoverflow question:

SQL Server Reporting Services Round() function

You are seeing Round to Even logic rather than mid point rounding.

This Wikipedia article has all the details about rounding: Wikipedia Rounding Article

Community
  • 1
  • 1
John Dyer
  • 2,316
  • 1
  • 21
  • 27