title: Using date of type char in where clause
body: <p>I have a SQL Server database table with a char column named "DATE" (I know, really bad, but I didn't create the database) that has dates stored in this format as strings: YYMMDD. I need to return records between these dates, so treat them as actual dates and I've tried every combination I know but still get errors. Any help is much appreciated. I've tried all of these with no luck: </p>
  
  <pre><code>SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 112) AS datetime) BETWEEN '100401' AND '101001'
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 12) AS datetime) BETWEEN '100401' AND '101001'
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 112) AS datetime) BETWEEN '100401' AND '101001'
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 12) AS datetime) BETWEEN '100401' AND '101001'
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 112) AS datetime) &gt;= 100401 AND CAST(CONVERT(char(8), [DATE], 112) AS datetime) &lt;= 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 12) AS datetime) &gt;= 100401 AND CAST(CONVERT(char(8), [DATE], 12) AS datetime) &lt;= 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 112) AS datetime) &gt;= 100401 AND CAST(CONVERT(char(8), "DATE", 112) AS datetime) &lt;= 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 12) AS datetime) &gt;= 100401 AND CAST(CONVERT(char(8), "DATE", 12) AS datetime) &lt;= 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST([DATE] AS INT) &gt;= 100401 AND CAST([DATE] AS INT) &lt; 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST("DATE" AS INT) &gt;= 100401 AND CAST("DATE" AS INT) &lt; 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST([DATE] AS INT) BETWEEN 100401 AND 101001
  SELECT * FROM caddb..INCIDENT WHERE CAST("DATE" AS INT) BETWEEN 100401 AND 101001
  </code></pre>