title: SQL Server 2008 : Varchar to Datetime conversion
body: <p>I have a <code>datetime</code> column <code>ArrivalDateTime</code> which is stored as a <code>varchar</code> value.</p>
  
  <p>Let's say if the value is <code>20161212093256</code>, I want the output to be <code>2016-12-12 09:32:56</code>.</p>
  
  <p>I could get the date part in datetime format as below.</p>
  
  <pre><code>SELECT 
      CONVERT(DATETIME2(0), LEFT('20161212093256', 8))
  </code></pre>
  
  <p>This returns the output as <code>2016-12-15 00:00:00</code>.</p>
  
  <p>I tried the following query to get the time part as well.</p>
  
  <pre><code>SELECT 
      CONVERT(DATE, LEFT('20161212093256', 8)) + ' ' + 
      CONVERT(TIME, RIGHT('20161212093256', 6))
  </code></pre>
  
  <p>But this throws an error:</p>
  
  <blockquote>
    <p>The data types date and varchar are incompatible in the add operator</p>
  </blockquote>
  
  <p>How can I get both date and time part in <code>datetime</code> format?</p>