CODEWITHSUNDEEP
sqlsql-serverroundingcurrency
Vadim
Vadim

Reputation: 21704

How to round correctly SQL money type?

I need help from SQL experts. I'm trying to get data with 4 decimal places. I'm doing some calculation on type "money". However, the calculation doesn't round to mine liking.

Below is an example with regular numbers and the same values of money type.

DECLARE @MaxAmt money
DECLARE @MinAmt money

SET @MaxAmt = 207998693.55
SET @MinAmt = 20799442.35

SELECT
((207998693.55 - 20799442.35) / 2 + 20799442.35) / 24 AS Col1,
((@MaxAmt - @MinAmt) / 2 + @MinAmt) / 24 AS Col2

Col1 displays value equals to 4766627.831250000 While Col2 has value 4766627.8312.

My goal is to display only 4 decimal digits but it should be round correctly. I would expect 4766627.8313 value.

Which would displayed after following casting.

SELECT CAST(((207998693.55 - 20799442.35) / 2 + 20799442.35) / 24 AS DECIMAL(12,4))

But when I deal with money type my value after a decimal is .8312 instead of .8313

Upvotes: 0

Views: 13309

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

The most likely explanation for this behavior is that the Money type is using what's known as Banker's Rounding (a review of that page should reveal the likely reasons this was chosen).

Skimming through the documentation, there doesn't seem to be anything 'built in' to change this - you'll likely need to cast to DECIMAL first, explicitly, if you want to perform this in SQL itself. (Alternatives include pulling at least some of the calculation out to your application layer, if necessary, or writing a stored procedure).

Upvotes: 3

Related Questions