Import Excel date column to SQL 2008

• muralidhar-412003

  Grasshopper

  Points: 18

  More actions

  November 17, 2011 at 4:45 am

  #390886

  I have some data in Excel which I have to import to a new sql table . Its failing on one coulmn which is a date column and the data is in the format eg: '1994_Sep_28'...Probably I have to do some conversion and I'm not sure how to and which datatype to use to pull in this date column data.. Does anyone know how to import this date column data into SQL 2008 ?

  Appreciate any assistance, Thx.

  Bala

• Stephen E. Cook

  SSCrazy

  Points: 2275

  More actions

  November 17, 2011 at 11:51 pm

  #1409692

  I'd stage it into a CHAR column and mung it into one of the accepted date input formats (see http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput) using SUBSTRING() and a CASE expression.

  Something along the lines of:

  CREATE TABLE dbo.date_test

  (

  ip CHAR(11),

  op DATE

  );

  INSERT INTO dbo.date_test (ip) VALUES ('1994_Sep_28'), ('2011_Nov_30'), ('2012_Dec_21');

  UPDATE dbo.date_test

  SET op = SUBSTRING(ip, 1, 4)

  + '-'

  + CASE SUBSTRING(ip, 6, 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'

  WHEN 'Dec' THEN '12'

  END

  + '-'

  + SUBSTRING(ip, 10, 2);

  SELECT * FROM dbo.date_test;

  -- Stephen Cook