title: Error Conversion failed when converting date and/or time from character string: varchar(8000)
body: <p>I have a column of type <code>varchar(8000)</code>. We save all kinds of data in it like number, text, date etc. </p>
  
  <p>I wrote a simple select query to get all "Date" values from the table all works, I can also use <code>CAST</code> or <code>Convert</code> to get my value in this format <code>2014-12-16 00:00:00.000</code>. </p>
  
  <p>Now I'm trying to add a <code>where</code> clause to filter this year's info, but I get an error:</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>When I use TOP 10000 in my select query the error goes away. This is really strange </p>
  
  <pre><code>; WITH TempTable AS 
  (
      SELECT 
          ID, CAST(Value AS DateTime) [SomeDate]
      FROM 
          SampleTable
      WHERE
          ColType = 'Date'
  )
  SELECT * 
  FROM FROM TempTable 
  WHERE [SomeDate] BETWEEN '1/1/2017' AND '12/30/2017'
  </code></pre>
  
  <p>This throws </p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <pre><code>; WITH TempTable AS 
  (
      SELECT TOP 100000 
          ID, CAST(Value AS DateTime) [SomeDate]
      FROM 
          SampleTable
      WHERE
          ColType = 'Date'
  )
  SELECT * 
  FROM FROM TempTable 
  WHERE [SomeDate] BETWEEN '1/1/2017' AND '12/30/2017'
  </code></pre>
  
  <p>This works fine. Please note I have only 25 some rows in my result. Top 100000 is just a big number I used. This is very strange how my TOP keyword is making my query good. </p>
  
  <p><strong>Update:</strong> </p>
  
  <p>Here is how I ended up resolving without using TOP keyword. I have split my query little bit as seen below. Thank you all for your time and comments. </p>
  
  <pre><code>;WITH GetAllIDs
  AS (
      SELECT ID
      FROM SampleTable
      WHERE ColType = 'Date'
      )
      ,FinalTable
  AS (
      SELECT ID
          ,(
              SELECT Cast([Value] AS DATETIME)
              FROM SampleTable dt
              WHERE dt.ID = tt.ID
                  AND dd.ColType = 'Date'
              ) [SomeDate]
      FROM GetAllIDs tt
      )
  SELECT *FROM FinalTable
  WHERE [SomeDate] BETWEEN '1/1/2017' AND '12/30/2017'
  </code></pre>