Money in SQL Server

Question

Im using entity framework as ORM and Sql Server 2008 as server.

I have financial operations in my app so that I used money column. But when result is for example 2.99986 it stores in database value 2.9998 while desired is 2.9999.

Is there any option to make it make it work just like math says and if not how can I round numbers in C# so that Math.Round(2.99986) would give as the result 2.9998, simply cuts the rest of sigits after 4th place.

Thanks for help

The only solution I came up with is to multiply my number by 10000 then truncate it and then divide by 10000 :) Any other better solutions ?

Answer 1

That's a limit of the datatype. Looks like it truncates anything after the 4th digit to the right of the decimal.

From MSDN - Using Monetary Data:

If an object is defined as money, it can contain a maximum of 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8.

And:

money and smallmoney are limited to four decimal points. Use the decimal data type if more decimal points are required.

---

Update:

From your comments it appears that you want to resolve this in C#. In that case, you can use one of the Math.Round overloads that takes a MidpointRounding enumeration that suites the rounding logic that you need.