Search code examples
c#exceloledb

convert datetime format in excel to time format when insert into sql server

I'm trying to import excel data to sql server database. Everything work fine except one column where the format in excel is was datetime and the datatype in my database was time. It was unable to convert datetime to time when i trying to insert the data.

My code was:

OleDbCommand exclCmd = new OleDbCommand("select * from [Sheet1$]", exclConnection);
exclConnection.Open(); 
OleDbDataReader exclReader = exclCmd.ExecuteReader(); 
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); 
sqlBulk.DestinationTableName = "test_excel"; 
sqlBulk.ColumnMappings.Add("col1", "col1");
sqlBulk.ColumnMappings.Add("col2", "col2"); 
sqlBulk.ColumnMappings.Add("col3", "col3"); 
sqlBulk.ColumnMappings.Add("col4", "col4"); 
sqlBulk.WriteToServer(exclReader);

i tried to use "select con1, col2, col3, convert(time,col4) as col4 from [Sheet1$]" bt the convert function was not workable.

Anyone can help me on these? Thanks.

Solution

    1. if staging table is an option, you could convert Excel date to string using TEXT(col4,"dd mmmm yyyy hh:mm:ss"). once the data is in staging table, you may write appropriate SQL statement to insert into correct table.

    2. I would use SSIS for this kind of task since that gives more control on data transformations.