title: Converting varchar data type to decimals in SQL Server with stored procedure errors
body: <p>A <code>Quantity</code> column cannot be changed, it should be a <code>decimal(18,5)</code>, and an older SSIS package would actually use VB code to do this</p>
  
  <pre><code> Dim q As Decimal
   q = Convert.ToDecimal(currentRow(16))
  </code></pre>
  
  <p>However, I am streamlining the process to import flat file into a new staging table    </p>
  
  <p>The value that is getting inserted into a database staging table is inserted as <code>varchar(40)</code>.</p>
  
  <p>Example of one of the values:</p>
  
  <pre><code>0000000191185.18200-    
  </code></pre>
  
  <p>I want to convert that on the fly within a SQL Server query to a <code>decimal(18,5)</code>  value in order to insert it into another final table.</p>
  
  <p>Is it accurate to say that the conversion from <code>varchar</code> to <code>decimal(18,5)</code> should yield the value of -191185.18200 ?</p>
  
  <p>I am trying to test casting of this and I get an error in T-SQL </p>
  
  <pre><code>select CAST(Quantity AS DECIMAL(18, 5)) 
  from [DST].[DstPositionsStagingT]
  </code></pre>
  
  <p>Error is </p>
  
  <blockquote>
    <p>Error converting data type varchar to numeric.</p>
  </blockquote>
  
  <p><code>Quantity</code> is the column in the table holding the value. What am I doing wrong?</p>