Convert VARCHAR to SMALLDATETIME as part of UPDATE statement

Question

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.

Answer 1

DECLARE @s TABLE([date] VARCHAR(20));

INSERT @s SELECT '200603010929';

UPDATE @s SET [date] = CONVERT(CHAR(16), CONVERT(SMALLDATETIME, 
  LEFT([date],4) + SUBSTRING([date],7,2) + SUBSTRING([date],5,2) 
  + ' ' + STUFF(RIGHT([date],4),3,0,':')), 120);

SELECT [date], CONVERT(SMALLDATETIME, [date]) FROM @s;

If it is, in fact, YYYYMMDD, then it is slightly simpler:

DECLARE @s TABLE([date] VARCHAR(20));

INSERT @s SELECT '200603010929';

UPDATE @s SET [date] = CONVERT(CHAR(16), CONVERT(SMALLDATETIME, 
  LEFT([date],8) + ' ' + STUFF(RIGHT([date],4),3,0,':')), 120);

SELECT [date], CONVERT(SMALLDATETIME, [date]) FROM @s;