title: SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
body: <p>I have been getting the following error when running a SQL to convert my data type value from <code>varchar</code> to <code>datetime</code>.</p>
  
  <blockquote>
    <p>Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.</p>
  </blockquote>
  
  <p>I have checked the data and can't see anything to odd: Ran the following checks and all returning no results</p>
  
  <pre><code>SELECT [Date] from table where [DATe] is null
  SELECT [Date] from table where [DATe] = ''
  SELECT [Date] from table where LEN([date])&gt; 10
  SELECT [Date] from table where LEN([date])&lt; 10
  SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int, SUBSTRING([date],4,2)) &lt; 1 or convert(int, SUBSTRING([date],4,2)) &gt; 12
  SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) &lt; 1 or convert(int, SUBSTRING([date],4,2)) &gt; 31
  </code></pre>
  
  <p>Is there anything else worth looking at and maybe worth any pointers or help with this issue? Can't seem to get bottom of it.</p>