如何截断sql server 2000中的十进制值

I have a expression:

我有一个表达:

Select (2345789 * 39.456) / 100

The output is 925554.5078400.

输出为925554.5078400。

I would like to display 925554.50 i.e. 2 values after decimal.

我想显示925554.50,即小数点后的2个值。

If I use round

如果我使用圆形

Select round((2345789 * 39.456)/100, 1)

the output is 925554.5000000. But I want exactly 2 digits after the decimal. How to do that?

输出为925554.5000000。但我想要小数点后正好2位数。怎么做?

4 个解决方案

#1

Stating the obvious, but 925554.5000000 and 925554.50 are the same number.

说明显,但925554.5000000和925554.50是相同的数字。

If you want to display that number with two decimal places then that is an issue for your UI code, not your database.

如果要显示带有两个小数位的数字,则这是UI代码的问题,而不是数据库的问题。

Having said that, if you must do this in the database itself then try something like:

话虽如此,如果您必须在数据库本身中执行此操作,请尝试以下操作:

-- 1 decimal place
SELECT CAST(ROUND(@yourNumber, 1, 1) AS DECIMAL(18, 1))

-- 2 decimal places
SELECT CAST(ROUND(@yourNumber, 2, 1) AS DECIMAL(18, 2))

-- 3 decimal places
SELECT CAST(ROUND(@yourNumber, 3, 1) AS DECIMAL(18, 3))

-- 4 decimal places
SELECT CAST(ROUND(@yourNumber, 4, 1) AS DECIMAL(18, 4))

#2

I have solution also use truncate value

我有解决方案也使用截断值

Select (2345789 * 39.456)/100  result-925554.5078400
select cast (ROUND((2345789 * 39.456)/100 ,2,1)as decimal(18,2)) result-925554.50
select cast (ROUND((2345789 * 39.456)/100 ,3,2)as decimal(18,3)) result-925554.507
select cast (ROUND((2345789 * 39.456)/100 ,4,3)as decimal(18,4)) result-925554.5078

ok. I hope you understand from this.

好。我希望你从中理解。

#3

Try (not tested):

尝试(未测试):

SELECT CAST(number as decimal(18,2))

#4

See:

select ROUND(12345.67890,-4) --result: 10000.00000
select ROUND(12345.67890,-3) --result: 12000.00000
select ROUND(12345.67890,-2) --result: 12300.00000
select ROUND(12345.67890,-1) --result: 12350.00000
select ROUND(12345.67890,0)  --result: 12346.00000
select ROUND(12345.67890,1)  --result: 12345.70000
select ROUND(12345.67890,2)  --result: 12345.68000
select ROUND(12345.67890,3)  --result: 12345.67900
select ROUND(12345.67890,4)  --result: 12345.67890

select ROUND(12345.67890,-4,1) --result: 10000.00000
select ROUND(12345.67890,-3,1) --result: 12000.00000
select ROUND(12345.67890,-2,1) --result: 12300.00000
select ROUND(12345.67890,-1,1) --result: 12340.00000
select ROUND(12345.67890,0,1)  --result: 12345.00000
select ROUND(12345.67890,1,1)  --result: 12345.60000
select ROUND(12345.67890,2,1)  --result: 12345.67000
select ROUND(12345.67890,3,1)  --result: 12345.67800
select ROUND(12345.67890,4,1)  --result: 12345.67890

You can use:

您可以使用:

select ROUND(12345.67890,1)  --result: 12345.70000

Or:

select ROUND(12345.67890,1,0)  --result: 12345.70000

Note: ROUND ( numeric_expression , length [ ,function ] )

注意:ROUND(numeric_expression,length [,function])

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

是除了位数据类型之外的精确数字或近似数值数据类型类别的表达式。

length

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

是否对numeric_expression进行舍入的精度。 length必须是tinyint,smallint或int类型的表达式。当length为正数时,numeric_expression将四舍五入为length指定的小数位数。当length为负数时,numeric_expression在小数点的左侧舍入,由length指定。

function

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

是要执行的操作类型。 function必须是tinyint,smallint或int。省略function或者值为0(默认值)时,numeric_expression将四舍五入。如果指定了0以外的值,则会截断numeric_expression。

Reference: http://technet.microsoft.com/pt-br/library/ms175003.aspx

#1