title: SQL Server: casting the column datatype
body: <p>SQL Server 2005/ 2008</p>
  
  <p>I have kept a <code>CASE</code> condition for a SQL query which results Float /Numeric Value. One of the <code>CASE</code> condition is to say N/A. I kept the piece of code.</p>
  
  <p>For column : <code>Compliance</code> - Possible values could be N/A,100.0,99.1 ... ( nvarchar, float ).</p>
  
  <pre><code>select 
      x.MemberName, x.DOB, x.FilePath, 
      x.Medication, x.NDC, x.Directions, 
      x.Name, x.Strength, x.GenericName,
      x.QtyOrdered, x.DaysSupply, x.DateFilled, 
      CASE WHEN x.test = 0 THEN 'N/A' 
           WHEN compliance &gt; 100.0 THEN '100.0' 
           ELSE CAST(FLOOR(compliance * 10)/10.0 AS DECIMAL(3, 1)) 
      END AS [Compliance]
  </code></pre>
  
  <p>Above syntax spills error as ..</p>
  
  <blockquote>
    <p>Msg 8114, Level 16, State 5, Line 10<br>
    Error converting data type varchar to numeric.</p>
  </blockquote>
  
  <p>How should I type cast the field ?</p>