title: SQL - char MDDYY or MMDDYY `to datetime
body: <p>First of all, whats going on! First time reader, first time poster. Now that the formality's are out of the way, down to the nitty gritty.</p>
  
  <p>I copied data from our core system (as400) to a SQL database so we can run our own transaction reports. I mirrored the field types to avoid any import issues, and for that reason the date field is a decimal. The date is as follows MDDYY Jan - Sep MMDDYY Oct - Dec. Heres what I have so far to be able to convert this to a date time.</p>
  
  <pre><code>select 
  wtdate [og_date]
  ,len (wtdate) [length]
  ,case when 
          len(cast(replace(wtdate,' ','') as char)) = 5 
        then
          CONVERT(DATe,LEFT((CONVERT(VARCHAR,'0') + CONVERT(VARCHAR, WTDATE)),2)+'/'+SUBSTRING((CONVERT(VARCHAR,'0') + CONVERT(VARCHAR, WTDATE)),3,2)+'/'+ '20' + RIGHT((CONVERT(VARCHAR,'0') + CONVERT(VARCHAR, WTDATE)),2),110)
        when 
          len(cast(replace(wtdate,' ','') as char)) = 6 
        then 
          LEFT(WTDATE,2)+'/'+SUBSTRING(CAST(WTDATE AS VARCHAR),3,2)+'/'+ '20' + RIGHT(WTDATE,2)
          --cast(wtdate as varchar)
        else
          'Error converting date'
   end as [convert datetime]
  from 
  Trans
  group by wtdate
  order by wtdate      
  
  
  results
  og_date length  convert datetime
  92514   5   2014-09-25
  92614   5   2014-09-26
  92914   5   2014-09-29
  93014   5   2014-09-30
  100114  6   2014-10-01
  100214  6   2014-10-02
  100314  6   2014-10-03
  100614  6   2014-10-06
  100714  6   2014-10-07    
  </code></pre>
  
  <p>The issue I'm having: Why is the whole column being converted to a date when i am only converting the ones where the length is 5 characters. It seems like it is completely ignoring the case when the length is equal to 6. Any help would be appreciated. 
  Thank You.</p>
  
  <p>Also I am using openquery to get the information and am not sure how to convert the date before I import the data. Just throwing that out there in case someone asks why i don't just do that.</p>