title: Convert varchar MMDDYYYY to MM/DD/YYYY datetime and select the most recent date only
body: <p>Okay, so I have kind of a weird issue... the dates in the table have been entered in as string values <code>MMDDYYYY</code> and I'm trying to have the displayed as <code>MM/DD/YYYY</code> in a report and only select the most recent date pertaining to an ID, because some ID's may have multiple dates.</p>
  
  <p>Example of my table:</p>
  
  <pre><code>  ID  |  MyDate  |
  ------+----------+
    1   | 01302014 |
    1   | 04222014 |
    2   | 01302014 |
  </code></pre>
  
  <p>What I want to see when I select and insert into a temp table is this:</p>
  
  <pre><code>  ID  |   MyDate  |
  ------+-----------+
    1   | 4/22/2014 |
    2   | 1/30/2014 |
  </code></pre>
  
  <p>I know that storing dates as string values is a poor practice especially when storing them as <code>MMDDYYYY</code>, but does anyone have a solution to this nightmare?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I forgot to mention that some fields might be NULL. Not sure if that makes a difference or not, but I think it does if I try to flip the dates using Right, Left, Convert.</p>