In SQL how can I convert a money datatype to a decimal?

Question

I want to convert a money datatype to a decimal, because I want to record the results to 8 decimal places.

For example, in a currency rate table I see the rate stored as 2871047428.20 as a money datatype; using Microsoft SQL Management Studio, I want to divide that by 10000000 in order to achieve the result 287.10474282; however the result I am actually getting is 287.1047.

I believe the reason I am getting only the 4 decimal places is because it is money datatype, and therefore I think the way forward is to convert this to a decimal datatype....

Answer 1

SELECT CAST(currency_rate AS decimal) / 10000000 FROM ...

Answer 2

Here a comparison left column the decimal value, right column the calculated money value:

DECLARE @money AS money
SET @money = 2871047428.20
SELECT CAST(@money AS decimal(34,4)) / 10000000, @money / 10000000

See also here on Stack Overflow:

• How do I convert from a money datatype in SQL server?
• SQL Server 2005 varchar loses decimal places converting to a decimal

Answer 3

splattne's answer is almost correct, except for two minor changes:

DECLARE @money AS money
SET @money = 2871047428.20
SELECT CAST(@money AS decimal(34,6)) / 10000000.0, @money / 10000000.0

This will give the correct answer: 287.10474282. What I did is changing the precision value and adding the ".0" to the division value.

The only thing that puzzles me right now is the fact I have to cast the value as decimal(34,6) instead of the expected decimal(34,8).

So, I wrote the query in another way, which makes more sense to me:

DECLARE @money AS money
SET @money = 2871047428.20
SELECT CAST((@money / 10000000.0) AS decimal(34,8)), @money / 10000000.0

Please see which one works for you.

Answer 4

You're all hitting oddities of decimal division Here is my answer to another thread, T-SQL Decimal Division Accuracy

And you may have implicit float conversions too because of data type precedence if you use 10000000.0

DECLARE @money AS money
SET @money = 2871047428.20
--Oddities
SELECT
    CAST(@money AS decimal(34,8)) / 10000000,
    CAST(@money AS decimal(34,8)) / 10000000.0,
    CAST(@money AS decimal(34,8)) / 10000000.00,
    CAST(@money AS decimal(34,8)) / 10000000.000,
    CAST(@money AS decimal(34,8)) / 10000000.0000
--Should be safe. My brain hurts if I work through p and s
SELECT
    CAST(@money AS decimal(38,8)) / CAST(10000000 AS decimal(8,0))