blocks|key|1558083|text|select+round(123.456,+2,+1)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1558084|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select round(123.456, 2, 1)
</code></pre>

blocks|key|1773443|text|SELECT+Cast(Round(123.456,2,1)+as+decimal(18,2))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1773444|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT Cast(Round(123.456,2,1) as decimal(18,2))
</code></pre>

blocks|key|1773379|text|以下是我能够截断而不是舍入的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773380|select+100.0019-(100.0019%25.001)|code-block|syntax|javascript|1773381|返回100.0010|1773382|还有你的例子：|1773383|select+123.456-(123.456%25.001)|1773384|返回123.450|1773385|现在，如果你想去掉结尾的零，只需对它进行转换：|1773386|select+cast((123.456-(123.456%25.001))+as+decimal+(18,2))|1773387|返回123.45|1773388|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Here's the way I was able to truncate and not round: 

<pre><code>select 100.0019-(100.0019%.001)
</code></pre>

returns 100.0010

And your example: 

<pre><code>select 123.456-(123.456%.001)
</code></pre>

returns 123.450

Now if you want to get rid of the ending zero, simply cast it:

<pre><code>select cast((123.456-(123.456%.001)) as decimal (18,2))
</code></pre>

returns 123.45

blocks|key|1773732|text|实际上，无论第三个参数是0、1还是2，它都不会对您的值进行舍入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773733|CAST(ROUND(10.0055,2,0)+AS+NUMERIC(10,2))|code-block|syntax|javascript|1773734|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Actually whatever the third parameter is, 0 or 1 or 2, it will not round your value.

<pre><code>CAST(ROUND(10.0055,2,0) AS NUMERIC(10,2))
</code></pre>

blocks|key|1773303|text|你想不想要小数点？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773304|如果不是，请使用|1773305|select+ceiling(@value),floor(@value)|code-block|syntax|javascript|1773306|如果你用0做，那么做一个舍入：|1773307|select+round(@value,2)|1773308|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Do you want the decimal or not?

If not, use 

<pre><code>select ceiling(@value),floor(@value)
</code></pre>

If you do it with 0 then do a round:

<pre><code>select round(@value,2)
</code></pre>

blocks|key|1773586|text|Round有一个可选参数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773587|Select+round(123.456,+2,+1)++will+=+123.45
Select+round(123.456,+2,+0)++will+=+123.46|code-block|syntax|javascript|1773588|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Round has an optional parameter

<pre><code>Select round(123.456, 2, 1) will = 123.45
Select round(123.456, 2, 0) will = 123.46
</code></pre>

blocks|key|1558234|text|另一个截断，没有舍入的解决方案和例子。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558235|++++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|code-block|syntax|javascript|1558236|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another truncate with no rounding solution and example.

<pre><code> 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
</code></pre>

blocks|key|1773666|text|这将删除任何数字的小数部分|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773667|SELECT+ROUND(@val,0,1)|code-block|syntax|javascript|1773668|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will remove the decimal part of any number

<pre><code>SELECT ROUND(@val,0,1)
</code></pre>

blocks|key|1558694|text|SELECT+CAST(Value+as+Decimal(10,2))+FROM+TABLE_NAME;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1558695|会给出小数点后的2个值。(MS+SQL+SERVER)|unstyled|1558696|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT CAST(Value as Decimal(10,2)) FROM TABLE_NAME;
</code></pre>

Would give you 2 values after the decimal point. (MS SQL SERVER)

blocks|key|1558799|text|ROUND(number,+decimals,+operation)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1558800|需要number+=>。要四舍五入的数字|unstyled|1558801|需要小数=>。要将数字舍入到的小数位数|1558802|操作=>可选。如果为0，则将结果四舍五入为小数。如果值不是0，则将结果截断为小数位数。默认值为0|1558803|SELECT+ROUND(235.415,+2,+1)|1558804|将为您提供235.410|offset|length|style|CODE|1558805|SELECT+ROUND(235.415,+0,+1)|1558806|将为您提供235.000|1558807|但是现在你可以使用cast来修剪0|1558808|SELECT+CAST(ROUND(235.415,+0,+1)+AS+INT)|1558809|将为您提供235|1558810|entityMap^0|0|0|0|0|0|5|7|0|0|5|7|0|9|4|G|1|0|0|5|3|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|15|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|16|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|19|8|@$O|1A|P|1B|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$B|C]]|$1|U|3|V|5|F|7|1D|8|@$O|1E|P|1F|Q|R]]|9|@]|A|$]]|$1|W|3|X|5|F|7|1G|8|@$O|1H|P|1I|Q|R]|$O|1J|P|1K|Q|R]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$B|C]]|$1|10|3|11|5|F|7|1M|8|@$O|1N|P|1O|Q|R]]|9|@]|A|$]]|$1|12|3|-4|5|F|7|1P|8|@]|9|@]|A|$]]]|13|$]]

<pre><code>ROUND(number, decimals, operation)
</code></pre>
number =&gt; Required. The number to be rounded 
decimals =&gt; Required. The number of decimal places to round number to 
operation =&gt; Optional. If 0, it rounds the result to the number of decimal. If another value than 0, it truncates the result to the number of decimals. Default value is 0
<pre class="lang-sql prettyprint-override"><code>SELECT ROUND(235.415, 2, 1)
</code></pre>
will give you <code>235.410</code>
<pre class="lang-sql prettyprint-override"><code>SELECT ROUND(235.415, 0, 1)
</code></pre>
will give you <code>235.000</code>
But now trimming<code>0</code> you can use <code>cast</code>
<pre class="lang-sql prettyprint-override"><code>SELECT CAST(ROUND(235.415, 0, 1) AS INT)
</code></pre>
will give you <code>235</code>

blocks|key|1773791|text|我知道这已经很晚了，但我不认为这是一个答案，我已经使用这个技巧多年了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773792|只需从值中减去.005，然后使用.005(@num，2)。|1773793|您的示例：|1773794|declare+@num+decimal(9,5)+=+123.456

select+round(@num-.005,2)|code-block|syntax|javascript|1773795|返回123.45|1773796|它会自动将四舍五入调整为您要查找的正确值。|1773797|顺便问一下，你是从电影《办公空间》中重新创建程序的吗？|1773798|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I know this is pretty late but I don't see it as an answer and have been using this trick for years.

Simply subtract .005 from your value and use Round(@num,2).

Your example:

<pre><code>declare @num decimal(9,5) = 123.456

select round(@num-.005,2)
</code></pre>

returns 123.45

It will automatically adjust the rounding to the correct value you are looking for.

By the way, are you recreating the program from the movie Office Space?

blocks|key|1558711|text|试着这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558712|SELECT+cast(round(123.456,2,1)+as+decimal(18,2))+|code-block|syntax|javascript|1558713|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try like this:
<pre><code>SELECT cast(round(123.456,2,1) as decimal(18,2)) 
</code></pre>

blocks|key|1773490|text|请尝试使用此代码将点后的3个十进制值转换为2个小数位：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773491|declare+@val+decimal+(8,+2)
select+@val+=+123.456
select+@val+=++@val

select+@val|code-block|syntax|javascript|1773492|输出为123.46|1773493|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Please try to use this code for converting 3 decimal values after a point into 2 decimal places:

<pre><code>declare @val decimal (8, 2)
select @val = 123.456
select @val = @val

select @val
</code></pre>

The output is 123.46

blocks|key|1773638|text|我认为您只需要十进制值，在这种情况下，您可以使用以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773639|declare+@val+decimal+(8,+3)
SET+@val+=+123.456

SELECT+@val+-+ROUND(@val,0,1)|code-block|syntax|javascript|1773640|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think you want only the decimal value,
in this case you can use the following:

<pre><code>declare @val decimal (8, 3)
SET @val = 123.456

SELECT @val - ROUND(@val,0,1)
</code></pre>

blocks|key|1558642|text|我知道这个问题真的很老，但是没有人使用子字符串来舍入。这是因为能够对非常长的数字进行舍入(+SQL+server中的字符串限制通常为8000个字符)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558643|SUBSTRING('123.456',+1,+CHARINDEX('.',+'123.456')+%2B+2)|code-block|syntax|javascript|1558644|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I know this question is really old but nobody used sub-strings to round. This as advantage the ability to round really long numbers (limit of your string in SQL server which is usually 8000 characters):

<pre><code>SUBSTRING('123.456', 1, CHARINDEX('.', '123.456') + 2)
</code></pre>

blocks|key|1774086|text|如果您希望获取某个数字(如89.0904987+)，并通过简单地省略不需要的小数位将其转换为89.09，只需使用以下命令：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1774087|select+cast(yourColumnName+as+decimal(18,2))|code-block|syntax|javascript|1774088|下面的屏幕截图来自W3Schools+SQL+Data+Types部分，其中描述了decimal(18,2)正在做的事情：|1774089|​|1774090|📷|atomic|1774091|1774092|因此，|1774093|select+cast(89.0904987+as+decimal(18,2))|1774094|为您提供：89.09|1774095|entityMap|0|LINK|mutability|MUTABLE|url|https://www.w3schools.com/sql/sql_datatypes.asp|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/0876c033b681254a6e0f460a351c2e84.png|imageAlt^0|D|A|1A|5|0|0|15|D|9|O|0|0|0|0|1|1|0|0|0|0|5|5|0^^$0|@$1|2|3|4|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1H|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@$9|1L|A|1M|1|1N]]|E|$]]|$1|M|3|N|5|6|7|1O|8|@]|D|@]|E|$]]|$1|O|3|P|5|Q|7|1P|8|@]|D|@$9|1Q|A|1R|1|1S]]|E|$]]|$1|R|3|N|5|6|7|1T|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1U|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|1V|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1W|8|@$9|1X|A|1Y|B|C]]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|1Z|8|@]|D|@]|E|$]]]|Z|$10|$5|11|12|13|E|$14|15]]|16|$5|17|12|18|E|$19|1A|1B|-4]]]]

If you desire to take some number like <code>89.0904987</code> and turn it into <code>89.09</code> by simply omitting the undesired decimal places, simply use the following:
<pre><code>select cast(yourColumnName as decimal(18,2))
</code></pre>
The following screenshot is from <a href="https://www.w3schools.com/sql/sql_datatypes.asp" rel="nofollow noreferrer">W3Schools SQL Data Types</a> section, which describes what <code>decimal(18,2)</code> is doing:
<a href="https://i.stack.imgur.com/MNlhq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MNlhq.png" alt="Screenshot from https://www.w3schools.com/sql/sql_datatypes.asp" /></a>
Therefore,
<pre><code>select cast(89.0904987 as decimal(18,2))
</code></pre>
gives you: <code>89.09</code>

blocks|key|1773536|text|我认为Mod(x,1)是最简单的方法。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1773537|entityMap^0|3|8|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>Mod(x,1)</code> is the easiest way I think.

blocks|key|1558075|text|select+convert(int,@value)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1558076|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select convert(int,@value)
</code></pre>

I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example:

<pre><code>declare @value decimal(18,2)

set @value = 123.456
</code></pre>

This will automatically round <code>@value</code> to be <code>123.46</code>, 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 <code>left()</code> function and convert back to a decimal. Are there any other ways?

Truncate (not round) decimal places in SQL Server

我正在尝试确定在SQL中截断或删除额外小数位而不进行四舍五入的最佳方法。例如：declare @value decimal(18,2)set @value = 123.456这将自动将@value舍入为123.46，这在大多数情况下都很好。然而，对于这个项目，我不需要它。有没有一种简单的方法可以截断我不需要的小数？我知道我可以使用left()函数并将其转换回小数。还有别的办法吗？

问在SQL Server中截断(非舍入)小数位数
EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server中截断(非舍入)小数位数EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server中截断(非舍入)小数位数
EN