Dates imported from excel.
- While importing from an excel file, I get the error :
Row XX failed to insert, error: Conversion failed when converting
datetime from character string.
The problem occurs when converting from a column that its category in
excel is date and the destination column has datetime type. All the
dates in my excel are dd/mm/yyyy format.
What is the date time format of the database? Are the values stored as dates or text in the Excel file? Check by changing the cell format to a number format, date values will appear as decimal numbers when this is done.
I do two steps when inserting dates. I run “SET DATEFORMAT YMD” on the connection to let SQL server know my string date format. I then format date times to a string for the insert using a YYYY-MM-DD format.
If all of the above items check out then to gather further information before running Import turn on SQL Trace from the Tools/SQL Trace menu. I recommend sending output to a File. Run import, get the error and end import. Turn off SQL Trace. Then you can load the trace file (Tools/SQL Trace/Show Logfile) and send me a copy directly and I will investigate some more. A support bundle will also prompt to include the trace file if it exists and you can send me that instead if you wish.