0

Here is the result from linqpad.

decimal d = .035m + .035m + .035m; 
d.Dump();
decimal.Round(d,2).Dump();
0.105
0.10

Here is the result from SMSS.

declare @d money
set @d = convert(money, .035) + convert(money, .035) + convert(money, .035)
select @d, round(@d,2)
(No column name)    (No column name)
0.105   0.11

What gives?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tim
  • 1,371
  • 3
  • 19
  • 30
  • `money` data type is deprecated long time ago. That's the difference. – Stan Feb 06 '13 at 18:22
  • What else have you researched so far? Could it be that one defaults to "round even" (for 5) and the other defaults to "round up" (for 5)? – Aaron Kurtzhals Feb 06 '13 at 18:23
  • 4
    Looks like Round by default rounds down: http://stackoverflow.com/a/8844698/1073631 -- Try adding MidpointRounding.AwayFromZero – sgeddes Feb 06 '13 at 18:23
  • 1
    @sgeddes It doesn't round down, it rounds to even. But the solution is the same. –  Feb 06 '13 at 18:28

2 Answers2

1

The right answer is:

  • C#'s round rounds midpoint to the nearest even integer
  • T/SQL's round rounds midpoint up

The below answer is wrong but perhaps instructive
A SQL constant like 0.1 is a decimal not a float, like hvd comments.

In C#, you're entering 0.35m as an exact decimal constant with the m postfix.

But in SQL, you are converting 0.35 from float to money. Conversions from float are not exact. If you enter a money type using the prefix $, you get the same result as C#:

declare @d money
set @d = $0.035 + $0.035 + $0.035
select @d, round(@d,2)

Example at SQL Fiddle.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Decimal constants have `numeric` data type in SQL Server, according to [the documentation](http://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal), not `float`. –  Feb 06 '13 at 18:26
  • And your online example gives `0.11`, which is not the result C# gives. –  Feb 06 '13 at 18:29
  • @hvd: You're right. Any clue why `select 1.0 * 1 / 3` prints `0.333333` ? Since `1.0` is a decimal with 1 scale, I'd expect the result to be `0.3`. – Andomar Feb 06 '13 at 18:39
  • Contrary to the documentation (unless precision is not considered part of a data type), the result type of a division is not necessarily the type of either of `/`'s operands. Try `cast(1 as numeric(1, 0)) / cast(3 as numeric(1, 0))` for example. By my reading of the documentation, you're right, you should be getting `0.3`. –  Feb 06 '13 at 19:02
  • @hvd: Pretty unreal: [sql fiddle](http://sqlfiddle.com/#!6/d41d8/2363/2) This [MSDN article](http://msdn.microsoft.com/en-us/library/ms190476.aspx) explains the rules, even why `1.0/3` results in a 6-scale decimal. – Andomar Feb 06 '13 at 19:14
  • That's an interesting find, thanks for that. It seems to contradict [this page](http://msdn.microsoft.com/en-us/library/ms175009.aspx), which reads: "Result Types: Returns the data type of the argument with the higher precedence." Again, assuming precision is part of "data type". At least it is clear what actually happens now. –  Feb 06 '13 at 19:44
0

@sgeddes answered it above. Looks like Round by default rounds down: stackoverflow.com/a/8844698/1073631 -- Try adding MidpointRounding.AwayFromZero.

tim
  • 1,371
  • 3
  • 19
  • 30