I am having a problem getting my "Rounding to 2 decimal places" to behave as I would expect. Try this trivial example
我有一个问题，让我的“舍入到2位小数”表现得像我期望的那样。试试这个简单的例子吧
declare @num numeric (18,2)
declare @vat numeric (18,2)
set @num = 11729.83
set @vat = 1.14
select round(@num/@vat,2)
I am getting an answer of 10289.320000 but I should be getting 10289.33 . The full un rounded number is 10289.324561403508771929824561404 (unless my maths is completely off)
我得到10289.320000的答案，但我应该得到10289.33。完整的未舍入数字是10289.324561403508771929824561404（除非我的数学完全关闭）
6 个解决方案
#1
0
Try this
尝试这个
select cast(round(@num/@vat,3) as decimal(18,2))
#2
1
Round can either return a value lower than the original, or a value upper than the original. In fact it returns the value closest to the original.
Round可以返回低于原始值的值，也可以返回高于原始值的值。实际上它返回最接近原始值的值。
If you want to systematically round a number to its lower or upper value, you could then use FLOOR or CEILING (Thanks @GarethD for refreshing my memory on CEILING...)
如果你想系统地将数字舍入到它的较低或较高值，你可以使用FLOOR或CEILING（感谢@GarethD在CEILING上刷新我的记忆......）
select round(floor(100*@num/@vat)/100,2) -> lower value
select round(ceiling(100*@num/@vat)/100,2) -> upper value
Otherwise round will indeed return 10289.32 when the value is strictly lower than 10289.325 (which is the case here)
否则当值严格低于10289.325（这是这种情况）时，round确实会返回10289.32
#3
1
Try converting to a decimal:
尝试转换为小数：
select cast(round(@num / @vat, 2) as numeric(18, 2))
I advocate round() to be explicit about the conversion method.
我提倡使用round（）来明确转换方法。
#4
0
Try this
尝试这个
declare @num numeric (18,2)
declare @vat numeric (18,2)
set @num = 11729.83
set @vat = 1.14
select round(Convert(Decimal(18,3),(@num/@vat)),2)
#5
-1
I am not sure if I understand correctly. But maybe you've been looking for something like this:
我不确定我是否理解正确。但也许你一直在寻找这样的东西：
select round(round(round(round(@num / @vat, 5), 4), 3), 2)
#6
-1
You will need to convert it to the appropriate decimal type again:
您需要再次将其转换为适当的十进制类型：
SELECT CONVERT(DECIMAL(18,2), ROUND(@NUM/@VAT,2))
#1
0
Try this
尝试这个
select cast(round(@num/@vat,3) as decimal(18,2))
#2
1
Round can either return a value lower than the original, or a value upper than the original. In fact it returns the value closest to the original.
Round可以返回低于原始值的值，也可以返回高于原始值的值。实际上它返回最接近原始值的值。
If you want to systematically round a number to its lower or upper value, you could then use FLOOR or CEILING (Thanks @GarethD for refreshing my memory on CEILING...)
如果你想系统地将数字舍入到它的较低或较高值，你可以使用FLOOR或CEILING（感谢@GarethD在CEILING上刷新我的记忆......）
select round(floor(100*@num/@vat)/100,2) -> lower value
select round(ceiling(100*@num/@vat)/100,2) -> upper value
Otherwise round will indeed return 10289.32 when the value is strictly lower than 10289.325 (which is the case here)
否则当值严格低于10289.325（这是这种情况）时，round确实会返回10289.32
#3
1
Try converting to a decimal:
尝试转换为小数：
select cast(round(@num / @vat, 2) as numeric(18, 2))
I advocate round() to be explicit about the conversion method.
我提倡使用round（）来明确转换方法。
#4
0
Try this
尝试这个
declare @num numeric (18,2)
declare @vat numeric (18,2)
set @num = 11729.83
set @vat = 1.14
select round(Convert(Decimal(18,3),(@num/@vat)),2)
#5
-1
I am not sure if I understand correctly. But maybe you've been looking for something like this:
我不确定我是否理解正确。但也许你一直在寻找这样的东西：
select round(round(round(round(@num / @vat, 5), 4), 3), 2)
#6
-1
You will need to convert it to the appropriate decimal type again:
您需要再次将其转换为适当的十进制类型：
SELECT CONVERT(DECIMAL(18,2), ROUND(@NUM/@VAT,2))