title: T-SQL 2008 Convert Date and time string to datetime
body: <p>I have two columns in my table, one to capture time and one to capture date.  Unfortunately, both are varchar().  I need to take the two fields, concatenate them together, and then convert them to datetime.</p>
  
  <p>I am trying to accomplish that with this:</p>
  
  <pre><code>select CONVERT(datetime,(select txt_returned_date+' '+CONVERT(varchar(20),CONVERT(TIME,txt_time_returned))),126) 
  from table_name
  </code></pre>
  
  <p>I am getting this error message:</p>
  
  <pre><code>Conversion failed when converting date and/or time from character string.
  </code></pre>
  
  <p>The date is being captured as "20130308" as a string.  Time is being captures as "4:27 PM" as a string</p>
  
  <p>What I am doing here is converting the string of the time to TIME, then back to varchar.  Then I am concatenating them together.  This works by itself, but once I introduce the CONVERT(datetime) to the whole query, it is giving me the error.  </p>
  
  <p>Any help to try to accomplish this is helpful.  Thanks!</p>