在 SQL Server 中截断（非舍入）小数位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44046/
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
Truncate (not round) decimal places in SQL Server
提问by Ryan Eastabrook
I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example:
我正在尝试确定在不舍入的情况下截断或删除 SQL 中额外小数位的最佳方法。例如：
declare @value decimal(18,2)
set @value = 123.456
This will automatically round @valueto be 123.46, which is good in most cases. However, for this project, I don't need that. Is there a simple way to truncate the decimals I don't need? I know I can use the left()function and convert back to a decimal. Are there any other ways?
这将自动舍@value入为123.46，这在大多数情况下都很好。但是，对于这个项目，我不需要那个。有没有一种简单的方法来截断我不需要的小数？我知道我可以使用该left()函数并将其转换回十进制。还有其他方法吗？
采纳答案by Jimmy
select round(123.456, 2, 1)
回答by Jeff Cuscutis
ROUND ( 123.456 , 2 , 1 )
When the third parameter != 0it truncates rather than rounds
当第三个参数!= 0它截断而不是舍入
http://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx
Syntax
句法
ROUND ( numeric_expression , length [ ,function ] )
Arguments
参数
numeric_expressionIs an expression of the exact numeric or approximate numeric data type category, except for the bit data type.lengthIs the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.functionIs the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.
numeric_expression是精确数字或近似数字数据类型类别的表达式，位数据类型除外。length是 numeric_expression 要四舍五入的精度。length 必须是 tinyint、smallint 或 int 类型的表达式。当 length 为正数时，numeric_expression 将四舍五入为 length 指定的小数位数。当长度为负数时，numeric_expression 在小数点左侧四舍五入，如长度所指定。function是要执行的操作类型。函数必须是 tinyint、smallint 或 int。当函数被省略或值为 0（默认）时，numeric_expression 被四舍五入。指定 0 以外的值时，numeric_expression 将被截断。
回答by Jimmy
SELECT Cast(Round(123.456,2,1) as decimal(18,2))
回答by Jimmy
Here's the way I was able to truncate and not round:
这是我能够截断而不是圆的方式：
select 100.0019-(100.0019%.001)
returns 100.0010
返回 100.0010
And your example:
还有你的例子：
select 123.456-(123.456%.001)
returns 123.450
返回 123.450
Now if you want to get rid of the ending zero, simply cast it:
现在，如果您想去掉结尾的零，只需将其转换为：
select cast((123.456-(123.456%.001)) as decimal (18,2))
returns 123.45
返回 123.45
回答by Jai
Actually whatever the third parameter is, 0 or 1 or 2, it will not round your value.
实际上，无论第三个参数是 0 或 1 还是 2，它都不会舍入您的值。
CAST(ROUND(10.0055,2,0) AS NUMERIC(10,2))
回答by Quentin
Round has an optional parameter
Round 有一个可选参数
Select round(123.456, 2, 1) will = 123.45
Select round(123.456, 2, 0) will = 123.46
回答by SQLMenace
Do you want the decimal or not?
你要小数还是不要？
If not, use
如果没有，请使用
select ceiling(@value),floor(@value)
If you do it with 0 then do a round:
如果你用 0 来做，那么做一个回合：
select round(@value,2)
回答by James
Another truncate with no rounding solution and example.
另一个没有舍入解决方案和示例的截断。
Convert 71.950005666 to a single decimal place number (71.9)
1) 71.950005666 * 10.0 = 719.50005666
2) Floor(719.50005666) = 719.0
3) 719.0 / 10.0 = 71.9
select Floor(71.950005666 * 10.0) / 10.0
回答by Probal
This will remove the decimal part of any number
这将删除任何数字的小数部分
SELECT ROUND(@val,0,1)
回答by Dawood Zaidi
SELECT CAST(Value as Decimal(10,2)) FROM TABLE_NAME;
Would give you 2 values after the decimal point. (MS SQL SERVER)
会在小数点后给你 2 个值。(MS SQL 服务器)
