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....

Solution 1

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

Solution 2

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

Solution 3

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))

Comments

[splattne]

SELECT @exchangeReate = ... FROM ExchangeRate ... - BTW: Feel free to upvote and/or accept if it helped finding the solution. ;-)

[Admin]

Hi, I'm inching closer but still its not working correctly yet:- DECLARE @exchangeRate AS money SELECT @exchangeRate = CAST(Rate AS decimal(34,4)) / 10000000 FROM... Is this syntax right? Yes I will vote later, thanks again.