T-Sql从货币数据类型中删除小数点

Given the constraint of only using T-Sql in Sql Server 2005, is there a better way to remove the decimal point from a money datatype than a conversion to a varchar (here implicitly) and then a replace of the decimal point?

鉴于仅在Sql Server 2005中使用T-Sql的限制，是否有更好的方法从money数据类型中删除小数点，而不是转换为varchar（此处隐式），然后替换小数点？

Here is what I have currently.

这是我目前的情况。

SELECT REPLACE(1.23, '.', ''), REPLACE(19.99, '.', '')

Which returns the desired 123 and 1999, but I was wondering if there was a better way. Any thoughts?

返回所需的123和1999，但我想知道是否有更好的方法。有什么想法吗？

5 个解决方案

#1

Multiply by 100 and then convert to an int.

乘以100，然后转换为int。

#2

You should never ever use the money datatype to store monetary values. If you do any calculations you will get truncated results. Run the following to see what I mean

您永远不应该使用money数据类型来存储货币值。如果进行任何计算，您将得到截断结果。运行以下内容以查看我的意思

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult

Output: 2949.0000 2949.8525

产量：2949.0000 2949.8525

#3

Could you be a little more specific about the use case? Removing the decimal point from the representation is a little unusual given that you'll lose all information about the scale. Are you assuming that there will always be two digits? If so, you could simplify multiply by 100 and then round before converting to a string.

你能对这个用例更具体一点吗？从表示中删除小数点有点不寻常，因为您将丢失有关比例的所有信息。你假设总会有两位数吗？如果是这样，您可以简化乘以100，然后在转换为字符串之前进行舍入。

#4

Keep in mind that the money data type can have up to 4 digits past the decimal. Values with more than two digits might not work as expected for either your original solution or the x100 trick.

请记住，货币数据类型最多可包含小数点后4位数。对于原始解决方案或x100技巧，超过两位数的值可能无法正常工作。

#5

Here is the magic:

这是魔术：

DataFormatString="{0:c0}

This will remove the decimal places.

这将删除小数位。

#1