[SQL] Convert VARCHAR to SMALLDATETIME as part of UPDATE statement

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).

As it stands, if I select the 'smalldatetime' when creating the table, the data import will fail with this error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (Date).
​

As I understand it, for it to work the date must be a certain format to work.

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).

So, my question is: 1) Is this possible? 2) How do I implement it?

This is what I have so far:

UPDATE NYSE
SET [date]=CONVERT(smalldatetime, [date])


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?

I appreciate all comments. Thanks.

Continue reading...