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

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

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

<p>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?</p>

[{'answer_id': 44049, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': -1, 'selected': False, 'text': '<pre><code>select convert(int,@value)\n</code></pre>\n'}, {'answer_id': 44052, 'author': 'Jimmy', 'author_id': 4435, 'author_profile': 'https://Stackoverflow.com/users/4435', 'pm_score': 9, 'selected': True, 'text': '<pre><code>select round(123.456, 2, 1)\n</code></pre>\n'}, {'answer_id': 44063, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 3, 'selected': False, 'text': '<p>Do you want the decimal or not?</p>\n\n<p>If not, use </p>\n\n<pre><code>select ceiling(@value),floor(@value)\n</code></pre>\n\n<p>If you do it with 0 then do a round:</p>\n\n<pre><code>select round(@value,2)\n</code></pre>\n'}, {'answer_id': 44093, 'author': 'Jeff Cuscutis', 'author_id': 2277, 'author_profile': 'https://Stackoverflow.com/users/2277', 'pm_score': 8, 'selected': False, 'text': '<pre><code>ROUND ( 123.456 , 2 , 1 )\n</code></pre>\n<p><a href="https://learn.microsoft.com/sql/t-sql/functions/round-transact-sql#arguments" rel="noreferrer">When the third parameter <strong>!= 0</strong> it truncates rather than rounds.</a></p>\n<p><strong>Syntax</strong></p>\n<pre><code>ROUND ( numeric_expression , length [ ,function ] )\n</code></pre>\n<p><strong>Arguments</strong></p>\n<ul>\n<li><p><code>numeric_expression</code>\nIs an expression of the exact numeric or approximate numeric data\ntype category, except for the bit data type.</p>\n</li>\n<li><p><code>length</code>\nIs 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.</p>\n</li>\n<li><p><code>function</code>\nIs 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.</p>\n</li>\n</ul>\n'}, {'answer_id': 543876, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': "<p>Here's the way I was able to truncate and not round:  </p>\n\n<pre><code>select 100.0019-(100.0019%.001)\n</code></pre>\n\n<p>returns 100.0010</p>\n\n<p>And your example:  </p>\n\n<pre><code>select 123.456-(123.456%.001)\n</code></pre>\n\n<p>returns 123.450</p>\n\n<p>Now if you want to get rid of the ending zero, simply cast it:</p>\n\n<pre><code>select cast((123.456-(123.456%.001)) as decimal (18,2))\n</code></pre>\n\n<p>returns 123.45</p>\n"}, {'answer_id': 543922, 'author': 'James', 'author_id': 56753, 'author_profile': 'https://Stackoverflow.com/users/56753', 'pm_score': 3, 'selected': False, 'text': '<p>Another truncate with no rounding solution and example.</p>\n\n<pre><code>    Convert 71.950005666 to a single decimal place number (71.9)\n    1) 71.950005666 * 10.0 = 719.50005666\n    2) Floor(719.50005666) = 719.0\n    3) 719.0 / 10.0 = 71.9\n\n    select Floor(71.950005666 * 10.0) / 10.0\n</code></pre>\n'}, {'answer_id': 826659, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 6, 'selected': False, 'text': '<pre><code>SELECT Cast(Round(123.456,2,1) as decimal(18,2))\n</code></pre>\n'}, {'answer_id': 1067677, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Please try to use this code for converting 3 decimal values  after a point into 2 decimal places:</p>\n\n<pre><code>declare @val decimal (8, 2)\nselect @val = 123.456\nselect @val =  @val\n\nselect @val\n</code></pre>\n\n<p>The output is 123.46</p>\n'}, {'answer_id': 1603920, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': -1, 'selected': False, 'text': '<p><code>Mod(x,1)</code> is the easiest way I think.</p>\n'}, {'answer_id': 1725878, 'author': 'Quentin', 'author_id': 210016, 'author_profile': 'https://Stackoverflow.com/users/210016', 'pm_score': 3, 'selected': False, 'text': '<p>Round has an optional parameter</p>\n\n<pre><code>Select round(123.456, 2, 1)  will = 123.45\nSelect round(123.456, 2, 0)  will = 123.46\n</code></pre>\n'}, {'answer_id': 1769828, 'author': 'Mohamed', 'author_id': 215369, 'author_profile': 'https://Stackoverflow.com/users/215369', 'pm_score': 0, 'selected': False, 'text': '<p>I think you want only the decimal value,\nin this case you can use the following:</p>\n\n<pre><code>declare @val decimal (8, 3)\nSET @val = 123.456\n\nSELECT @val - ROUND(@val,0,1)\n</code></pre>\n'}, {'answer_id': 1887371, 'author': 'Probal', 'author_id': 229569, 'author_profile': 'https://Stackoverflow.com/users/229569', 'pm_score': 2, 'selected': False, 'text': '<p>This will remove the decimal part of any number</p>\n\n<pre><code>SELECT ROUND(@val,0,1)\n</code></pre>\n'}, {'answer_id': 20858286, 'author': 'Jai', 'author_id': 3070147, 'author_profile': 'https://Stackoverflow.com/users/3070147', 'pm_score': 4, 'selected': False, 'text': '<p>Actually whatever the third parameter is, 0 or 1 or 2, it will not round your value.</p>\n\n<pre><code>CAST(ROUND(10.0055,2,0) AS NUMERIC(10,2))\n</code></pre>\n'}, {'answer_id': 36606943, 'author': 'Lukasz Szozda', 'author_id': 5070879, 'author_profile': 'https://Stackoverflow.com/users/5070879', 'pm_score': 2, 'selected': False, 'text': '<p>Another way is <strong><code>ODBC TRUNCATE</code></strong> function:</p>\n\n<pre><code>DECLARE @value DECIMAL(18,3) =123.456;\n\nSELECT @value AS val, {fn TRUNCATE(@value, 2)} AS result\n</code></pre>\n\n<p><kbd><strong><a href="http://rextester.com/XABA61021" rel="nofollow"><code>LiveDemo</code></a></strong></kbd></p>\n\n<p>Output:</p>\n\n<pre><code>╔═════════╦═════════╗\n║   val   ║ result  ║\n╠═════════╬═════════╣\n║ 123,456 ║ 123,450 ║\n╚═════════╩═════════╝\n</code></pre>\n\n<p>Remark:</p>\n\n<p>I recommend using built-in <code>ROUND</code> function with 3rd parameter set to 1.</p>\n'}, {'answer_id': 41881349, 'author': 'KeithL', 'author_id': 3325290, 'author_profile': 'https://Stackoverflow.com/users/3325290', 'pm_score': 1, 'selected': False, 'text': "<p>I know this is pretty late but I don't see it as an answer and have been using this trick for years.</p>\n\n<p>Simply subtract .005 from your value and use Round(@num,2).</p>\n\n<p>Your example:</p>\n\n<pre><code>declare @num decimal(9,5) = 123.456\n\nselect round(@num-.005,2)\n</code></pre>\n\n<p>returns 123.45</p>\n\n<p>It will automatically adjust the rounding to the correct value you are looking for.</p>\n\n<p>By the way, are you recreating the program from the movie Office Space?</p>\n"}, {'answer_id': 49507245, 'author': 'tukan', 'author_id': 6059896, 'author_profile': 'https://Stackoverflow.com/users/6059896', 'pm_score': 0, 'selected': False, 'text': "<p>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):</p>\n\n<pre><code>SUBSTRING('123.456', 1, CHARINDEX('.', '123.456') + 2)\n</code></pre>\n"}, {'answer_id': 57759691, 'author': 'Ishwor Bhusal', 'author_id': 10480983, 'author_profile': 'https://Stackoverflow.com/users/10480983', 'pm_score': 0, 'selected': False, 'text': '<p>I think we can go much easier with simpler example solution found in Hackerrank:</p>\n\n<blockquote>\n  <p>Problem statement: Query the greatest value of the Northern Latitudes\n  (LAT_N) from STATION that is less than 137.2345. Truncate your answer\n  to 4 decimal places.</p>\n</blockquote>\n\n<pre><code>SELECT TRUNCATE(MAX(LAT_N),4)\nFROM STATION\nWHERE LAT_N &lt; 137.23453;\n</code></pre>\n\n<p>Solution Above gives you idea how to simply make value limited to 4 decimal points. If you want to lower or upper the numbers after decimal, just change 4 to whatever you want.</p>\n'}, {'answer_id': 57942938, 'author': 'Dawood Zaidi', 'author_id': 7938407, 'author_profile': 'https://Stackoverflow.com/users/7938407', 'pm_score': 2, 'selected': False, 'text': '<pre><code>SELECT CAST(Value as Decimal(10,2)) FROM TABLE_NAME;\n</code></pre>\n\n<p>Would give you 2 values after the decimal point. (MS SQL SERVER)</p>\n'}, {'answer_id': 64183589, 'author': 'Adrita Sharma', 'author_id': 7603109, 'author_profile': 'https://Stackoverflow.com/users/7603109', 'pm_score': 1, 'selected': False, 'text': '<p>Try like this:</p>\n<pre><code>SELECT cast(round(123.456,2,1) as decimal(18,2)) \n</code></pre>\n'}, {'answer_id': 65255378, 'author': 'Harshit Mahajan', 'author_id': 10566155, 'author_profile': 'https://Stackoverflow.com/users/10566155', 'pm_score': 3, 'selected': False, 'text': '<pre><code>ROUND(number, decimals, operation)\n</code></pre>\n<p>number =&gt; Required. The number to be rounded<br />\ndecimals =&gt; Required. The number of decimal places to round number to<br />\noperation =&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</p>\n<pre class="lang-sql prettyprint-override"><code>SELECT ROUND(235.415, 2, 1)\n</code></pre>\n<p>will give you <code>235.410</code></p>\n<pre class="lang-sql prettyprint-override"><code>SELECT ROUND(235.415, 0, 1)\n</code></pre>\n<p>will give you <code>235.000</code></p>\n<p>But now trimming<code>0</code> you can use <code>cast</code></p>\n<pre class="lang-sql prettyprint-override"><code>SELECT CAST(ROUND(235.415, 0, 1) AS INT)\n</code></pre>\n<p>will give you <code>235</code></p>\n'}, {'answer_id': 70211618, 'author': 'Andrey Vasilyev', 'author_id': 11099129, 'author_profile': 'https://Stackoverflow.com/users/11099129', 'pm_score': 1, 'selected': False, 'text': '<p>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:</p>\n<pre><code>select cast(yourColumnName as decimal(18,2))\n</code></pre>\n<p>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:</p>\n<p><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></p>\n<p>Therefore,</p>\n<pre><code>select cast(89.0904987 as decimal(18,2))\n</code></pre>\n<p>gives you: <code>89.09</code></p>\n'}]
['https://Stackoverflow.com/questions/44046', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/105/']