title: How to filter VARCHAR(50) date column in SQL Server
body: <p>I created a table using <code>BULK INSERT</code> from a CSV file. I tried with date but couldn't get through. It was giving me error, so tried with <code>TradeDate varchar(50)</code> and everything worked and saved  '11012012' in Tradedate column.</p>
  
  <p>But, when I tried to retrieve the data using select query:</p>
  
  <pre><code>SELECT * 
  FROM FundTranTest 
  WHERE CONVERT(DATETIME, RIGHT(TradeDate,4)  + Left(TradeDate, 2)  + SUBSTRING(TradeDate, 3, 2))
        &lt; '2012-12-29' 
  </code></pre>
  
  <p>I received this error:</p>
  
  <blockquote>
    <p>Msg 242, Level 16, State 3, Line 1<br>
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.</p>
  </blockquote>
  
  <p>How to get this done? Any help would be great.</p>
  
  <p>then tried to update table with adding the date column:</p>
  
  <pre><code>ALTER TABLE FundTranTest ADD TDate Date
  
  WITH Udata AS 
  ( select TradeDate as rcol 
    from FundTranTest
  )
  UPDATE FundTranTest SET TDate = CONVERT(DATETIME,RIGHT(rcol,4)+Left(rcol,2)+SUBSTRING(rcol,3,2)) 
  FROM Udata u
  where FundTranTest.TradeDate  = u.rcol
  </code></pre>
  
  <p>but its not working ....</p>