Date Time values in SQL Bulk Insert

Question

i am importing some excel files to sql server using c# console application with SqlBulkcopy and receiving following exception while importing date time values.

Exception: This exception is arising when we are going to do bulk copy, where one or more columns has datetime datatype. i think it has something to do with date format below is the error detail.

"The given value of type String from the data source cannot be converted to type datetime of the specified target column."

code snippet

  sExcelConnectionString  = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 12.0;HDR=No;IMEX=1;\"";
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
                    OleDbTransaction tran; 
                    OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
                    OleDbConn.Open();
                    OleDbDataReader dr = OleDbCmd.ExecuteReader();


                if (dr.HasRows && dr.FieldCount > 1)
                {
                     //DataTable dt = readReader(dr);
                    try
                    {

                        string sSqlConnectionString = connectionString.ToString();
                        string sClearSQL = "DELETE FROM " + sSQLTable;
                        SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
                        SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);

                        SqlConn.Open();
                        //SqlCmd.ExecuteNonQuery();
                        SqlConn.Close();
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
                        bulkCopy.DestinationTableName = sSQLTable;
                        bulkCopy.BulkCopyTimeout = 0;
                        while (dr.Read())
                        {

                            bulkCopy.WriteToServer(dr);
                            imported = true;

                        }
                        OleDbConn.Close();
                        if (valToField.ToString() != "")
                        {
                            sClearSQL = "DELETE FROM " + sSQLTable + " where " + valToField + "='" + valToRemove.ToString() + "'";
                            SqlCmd = new SqlCommand(sClearSQL, SqlConn);
                            SqlConn.Open();
                            SqlCmd.ExecuteNonQuery();
                        }
                        SqlConn.Close();

                    }
                    catch (Exception ex)
                    {

                        LogMessageToFile("Error While Inserting Data from : " + excelFilePath.ToString() + "  to table :  " + dbtable.ToString() + ex.Message.ToString());

                    }
                }

Please suggest the solution

Thanks

Is this your very own custom exception message ? some code and the real exception would be nice to have onboard — V4Vendetta, Jun 12 '12 at 07:03
You need to sanity check your file. Make sure the dates are in a sql friendly format. Such as `20120612` for today. If you can't ensure that the date format is correct, then you need to two stages; import that data into `VARCHAR()` fields in a holding table, then sanity check the data and copy it to the final destination table - converting during the copy. This way you may also chose to log the records that failed your sanity checks. — MatBailie, Jun 12 '12 at 07:13
On a seperate note, you should consider either making use of `using` blocks, or move your clean up commands (`SqlConn.Close();` etc) to the `finally` block of your `try/catch` — GarethD, Jun 12 '12 at 07:23
You should use `TRUNCATE TABLE` to [clear a table](http://dba.stackexchange.com/questions/14615/sql-server-empty-table-is-slow-after-deleting-all-12-million-records/14619#14619) — Remus Rusanu, Jun 12 '12 at 07:24

Rich Andrews · Answer 1 · 2012-06-12T07:36:18.950

2

The simple answer is to convert your excel dates to strings using the =TEXT(a1,"dd mmmm yyyy hh:mm:ss") function where a1 is a reference to the cell with the date in.

Note the four m's for the month; it's always best to use full month names and let SQL server deal with the date conversion just in case you have a date collation conflict.

As per comments below, this will work across cultures & languages =TEXT(a1,"yyyy-MM-ddTHH:mm:ss")

edited Jun 12 '12 at 07:36

answered Jun 12 '12 at 07:09

Rich Andrews

4,168
3
35
48

It's asking for trouble to use full month names, as it assumes both SQL Server and your .NET application are using the same culture. Instead use a canonical culture-insensitive format such as yyyy-MM-dd HH:mm:ss – Joe Jun 12 '12 at 07:14
Interesting point there; depends on your situation but it's more likely that the full format you suggest can get the month and the date the wrong way around dependent on the settings on the server. We've had this problem with incorrectly configured servers before, hence the full name idea, however we were only ever using english – Rich Andrews Jun 12 '12 at 07:21
no, that format is unambiguous, unaffected by SQL Server's "SET DATEFORMAT" or "SET LANGUAGE". There are other unambiguous formats which work equally well, e.g. ISO 8601 yyyy-MM-ddTHH:mm:ss – Joe Jun 12 '12 at 07:31
Link to the problem I referred to above http://blogs.msdn.com/b/sqlserverfaq/archive/2009/11/13/how-to-change-date-format-after-installing-sql-server.aspx – Rich Andrews Jun 12 '12 at 07:32
wow, love stackoverflow I never knew about the use of the T in the format http://stackoverflow.com/questions/5815954/independent-format-for-a-string-representation-of-date-time-value-for-ms-sql-se – Rich Andrews Jun 12 '12 at 07:34
@RichAndrews The problem you are referring to is `dd/MM/yyyy` vs `MM/dd/yyyy`. yyyy-MM-dd is not ambiguous because yyyy-dd-MM is not used in any culture. – GarethD Jun 12 '12 at 07:37
@GarethD I'm a little confused because I've seen this behaviour before and the example here http://stackoverflow.com/questions/5815954/independent-format-for-a-string-representation-of-date-time-value-for-ms-sql-se agrees – Rich Andrews Jun 12 '12 at 07:41
1

@Rich, you're right, I'm wrong, the format I specified is ambiguous - but you can use 'yyyyMMdd HH:mm:ss' or the ISO 8601 format 'yyyy-MM-ddTHH:mm:ss'. – Joe Jun 12 '12 at 07:48
2

@RichAndrews Apologies, you're right. I should have written yyyyMMdd not yyyy-MM-dd. e.g. 20120612 will always be 12th June 2012, no matter what the date settings. http://sqlfiddle.com/#!3/03593/2 – GarethD Jun 12 '12 at 07:50

score 1 · Answer 2 · answered Jun 12 '12 at 07:30

Don't reinvent the wheel. Use SSIS, is the right tool for this job. Create a flow that reads from the Excel source, do any transformation is appropriate, then save it the SQL Server using fast load. What you're trying to do is slower, less maintainable, less serviceable, lacks adaptability in case of changes, and ultimately incorrect (as your error shows). A proper import would have to properly validate and transform each record (eg. using Datetime.TryParse and change the output to a proper DateTime type).

score 0 · Answer 3 · answered Jun 12 '12 at 07:16

"The given value of type String from the data source cannot be converted to type datetime of the specified target column."

The error message is explicit: there is a column being returned from the source as a String that you're trying to insert into a DateTime target column.

Date Time values in SQL Bulk Insert

3 Answers3