title: Compare varchar date value in SQL Server 2014 not working
body: <p>I have a vendor DB where I am trying to COMPARE the date VARCHAR values stored in their DB via a CASE statement in my UPDATE statement but I'm having problems converting the HHS.StatusEfffectiveDate if in fact the value is less than 10/02/2017 as stated in my code below.  Any help/direction would be appreciated. Thank you. </p>
  
  <p>My date column (HHS.StatusEffectiveDate) is defined as a varchar(10).  Here is my sql code:</p>
  
  <pre><code>UPDATE HHS 
  SET HHS.StatusEffectiveDate = 
      (
      CASE
          WHEN CAST(HHS.StatusEffectiveDate AS DATE) &lt;= '10/02/2017' THEN '10/05/2017'
          ELSE CONVERT(VARCHAR(10), CAST(SVEY.Effective_Date AS DATE),101)
      END
      ),
      HHS.Enddate = SVEY.End_Date
  
  FROM Mcsfwisdtest.FRA.dbo.vw_HHSurvey SVEY
  INNER JOIN dbo.Focus_Export_Econ_DisadvantagedHHS HHS
  ON HHS.StudentID = SVEY.CustomerID
  </code></pre>
  
  <p>CORRECTION to CASE statement:</p>
  
  <pre><code>CASE
      WHEN CAST(SVEY.Effective_Date AS DATE) &lt;= '2017-10-02' THEN CONVERT(VARCHAR(10), '10/05/2017', 101)
      ELSE CONVERT(VARCHAR(10), CAST(SVEY.Effective_Date AS DATE),101)
  END
  </code></pre>