在 SQL 中，如何将货币数据类型转换为小数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/638290/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In SQL how can I convert a money datatype to a decimal?
提问by
I want to convert a money datatype to a decimal, because I want to record the results to 8 decimal places.
我想将货币数据类型转换为小数，因为我想将结果记录到小数点后 8 位。
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.
例如，在货币汇率表中，我将存储为 2871047428.20 的汇率视为货币数据类型；使用 Microsoft SQL Management Studio，我想将其除以 10000000 以获得结果 287.10474282；然而我实际得到的结果是 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....
我相信我只得到 4 个小数位的原因是因为它是货币数据类型，因此我认为前进的方法是将其转换为十进制数据类型......
采纳答案by splattne
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:
另请参阅堆栈溢出：
回答by alextansc
splattne's answer is almost correct, except for two minor changes:
splattne 的回答几乎是正确的，除了两个小改动：
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.
这将给出正确答案：287.10474282。我所做的是更改精度值并将“.0”添加到除法值。
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).
现在唯一让我困惑的是我必须将值转换为小数（34,6）而不是预期的小数（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.
请看看哪一种适合你。
回答by gbn
You're all hitting oddities of decimal division Here is my answer to another thread, T-SQL Decimal Division Accuracy
你们都遇到了十进制除法的奇怪之处 这是我对另一个线程 T-SQL 十进制除法精度的回答
And you may have implicit float conversions too because of data type precedenceif you use 10000000.0
如果您使用 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))
回答by David
SELECT CAST(currency_rate AS decimal) / 10000000 FROM ...
