Databases
StackOverflow2013
Postsdbo
PODecimal (10,9) variable can't hold the number 50 (SQL Server 2008)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODecimal (10,9) variable can't hold the number 50 (SQL Server 2008)
    primarykey
    data
    text
    <p>This one is pretty straightforward. Why does the code below cause the error below?</p> <pre><code>declare @dTest decimal(10, 9) set @dTest = 50 </code></pre> <p>Error:</p> <pre><code>Msg 8115, Level 16, State 8, Line 3 Arithmetic overflow error converting int to data type numeric. </code></pre> <p>According to the <a href="http://msdn.microsoft.com/en-us/library/ms187746%28v=sql.100%29.aspx" rel="nofollow noreferrer">MSDN documentation</a> on <code>decimal(p, s)</code>, <code>p</code> (or 10 in my case) is the "maximum total number of decimal digits that can be stored, <em>both to the left and to the right of the decimal point</em>" whereas <code>s</code> (or 9 in my case) is the "<em>maximum</em> number of decimal digits that can be stored <em>to the right</em> of the decimal point."</p> <p>My number, 50, has only 2 digits total (which less than the <em>maximum</em> 10), and 0 digits to the right of the decimal (which is less than the <em>maximum</em> 9), therefore it should work.</p> <p>I found <a href="https://stackoverflow.com/questions/2059134/why-sql-server-throws-arithmetic-overflow-error-converting-int-to-data-type-nume">this question</a> about essentially the same issue, but no one explained why the documentation seems to conflict with the behavior. It seems like the <code>s</code> dimension is actually being interpreted as the <em>fixed</em> number of digits to the right of the decimal, and being <em>subtracted</em> from the <em>p</em> number, which in my case leaves 10 - 9 = only 1 digit remaining to handle the left side.</p> <p>Can anyone provide a reasonable way to interpret the documentation as written to match the behavior?</p> <p><strong>EDIT:</strong></p> <p>I see some explanations below, but they don't address the fundamental problem with the wording of the docs. I would suggest this change in wording:</p> <p>For "p (precision)" change "The maximum total number of decimal digits that can be stored" to read "The maximum total number of decimal digits that <em>will</em> be stored".</p> <p>And for "s (scale)" change "The maximum number of decimal digits that can be stored to the right of the decimal point." to "The number of decimal digits that <em>will</em> be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point."</p> <p>I'm going to submit a bug report to Connect unless some one has a better explanation.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJordan Rieger
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODecimal (10,9) variable can't hold the number 50 (SQL Server 2008)
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. PODecimal (10,9) variable can't hold the number 50 (SQL Server 2008)
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. PODecimal (10,9) variable can't hold the number 50 (SQL Server 2008)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMS responded to my bug report, and has now updated the documentation for SQL Server 2012 as I suggested. See https://connect.microsoft.com/SQLServer/feedback/details/760495/. Take that, downvoters! :P
      singulars
      1. PODecimal (10,9) variable can't hold the number 50 (SQL Server 2008)
      1. USJordan Rieger
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload