title: column value is varchar which needs to be converted to decimal. column name is in date format. sql server
body: <p>I have a table <code>tblSample</code> which has columns names as date format value i.e. [202007], [202006]---[202001] and datatype is <code>varchar</code>.</p>
  <p>I want to update another column(X) of <code>tblSample</code> with [202006]-[202005] and column(Y) with [202005]-[202004].
  I am using below query which is giving output as 1 for all the records(around 50 records in <code>tblSample</code>).</p>
  <pre class="lang-sql prettyprint-override"><code>    UPDATE tblSample          
      SET
      X = CAST(ISNULL(SELECT LEFT(CONVERT(varchar, DATEADD(month, -3, GETDATE(), 112), 6)), 0) AS DECIMAL(18,2)) - CAST(ISNULL((SELECT LEFT(CONVERT(varchar, DATEADD(month, -4, GETDATE()), 112), 6)), 0) AS DECIMAL(18, 2))
  </code></pre>
  <p>I am using <code>LEFT(CONVERT(varchar, DATEADD(month, -3, GETDATE(), 112), 6))</code> since the column name value will be updated everymonth to current month.</p>
  <p>I have to convert to varchar to decimal since I am getting error saying subtraction cannot be performed on varchar values.</p>
  <p>Where is that I am going wrong and will my update query, updates column-X and column-Y value correct for each row in the table</p>
  <p>Values in columns of tblSample [2006]-3565.24, [2005]-3461.36, [2004]-3510.36.
  Output</p>
  <p>Thanks Shyam</p>