title: Convert VARCHAR to SMALLDATETIME as part of UPDATE statement
body: <p>I'm performing a bulk data import on CSV files. In the CSV files, my 'Date' column contains a date in the format 'YYYYDDMMHHMM' (e.g. 200603010929).</p>
  
  <p>As it stands, if I select the 'smalldatetime' when creating the table, the data import will fail with this error: </p>
  
  <blockquote>
    <p>Bulk load data conversion error (type mismatch or invalid character
    for the specified codepage) for row 2, column 2 (Date).</p>
  </blockquote>
  
  <p>As I understand it, for it to work the date must be a certain format to work.</p>
  
  <p>Therefore, one idea I have is to import the date values into a field of type VARCHAR, then when the import is finished I want to perform an UPDATE on every row to convert the date to a smalldatetime. This would lengthen the import process, but I think it would be much easier and much faster than attempting to do a find and replace on the CSV data (it's an enormous data set). </p>
  
  <p>So, my question is: 
  1) Is this possible? 
  2) How do I implement it? </p>
  
  <p>This is what I have so far: </p>
  
  <pre><code>UPDATE NYSE
  SET [date]=CONVERT(smalldatetime, [date])
  </code></pre>
  
  <p>This fails since the text isn't of the right format. Is it possible to specify the format of the string inside the CONVERT function, or is there some other way? </p>
  
  <p>I appreciate all comments. Thanks.</p>