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>

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

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

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

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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