title: How to convert a blank string to blank date?
body: <p>I have one column, data type is string, show the record like '                                                                                                                                                                     30MAY2014:00:00:00', some rows are blank. now I need to change the string to date, like '20140530', but the blank row still keep it as blank. I am using sql server 2012. here is my query:</p>
  
  <pre><code>select 
  CONVERT(VARCHAR,substring(ColumnName,6,4)) 
           +RIGHT('0'+ (CONVERT(VARCHAR,(CASE substring(ColumnName,3,3)
                                    WHEN 'JAN' THEN 01
                                    WHEN 'FEB' THEN 02
                                     WHEN 'MAR' THEN 03
                                     WHEN 'APR' THEN 04
                                     WHEN 'MAY' THEN 05
                                     WHEN 'JUN' THEN 06
                                     WHEN 'JUL' THEN 07
                                     WHEN 'AUG' THEN 08
                                     WHEN 'SEP' THEN 09
                                     WHEN 'OCT' THEN 10
                                     WHEN 'NOV' THEN 11
                                     ELSE 12
                                    END))),2)
         + CONVERT(VARCHAR,substring(ColumnName],1,2)) 
  FROM table
  </code></pre>
  
  <p>if there is records in the column, it is correct, but the blank row show 12.
  How to modify my query? 
  I still have one column, this month file shows all the rows are blank, I have to do the exactly same work. change the blank string to blank date, I try the same query, SQL server show the error: the Conversion failed when converting date and/or time from character string. 
  Any suggestions are appreciate.  </p>