SQL Server: Failed to execute query. Error: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 9 (DueOn)
TL:DR This is what happens if you have an invalid date format in your data source for a datetime when using T-SQL’s OPENROWSET or BULK INSERT command.
If you need to bulk load data into SQL Server then you have several options but assuming you can get that data as CSV then either OPENROWSET or BULK INSERT will handle this for you.
It will work for data on filing systems but also for data stored on Azure Storage Account Blob Containers and this is exceptionally useful, and when dealing with Azure SQL Databases much, much faster. However these commands really show their age as they come from a time of binary files and their formatting has grown over the years.
As we use them here at Simple Innovation we are finding a lot of errors that aren’t that explicit normally caused by mismatches between the data and format files.
One we found is:
Failed to execute query. Error: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 9 (DueOn)Here the error details are pretty good we know which row has the issue, row 7, and in which column it is, column 9 (DueOn).
So if we have a CSV file:
And an XML format file:
We can see the datetime used was:
27/10/2020 00:00:00
Which is the datetime format
dd/MM/yyyy hh:mm:ss
So we get the error:
Failed to execute query. Error: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 9 (DueOn)
For bulk import we need the following format
yyyy-MM-ddThh:mm:ss.xxxxxx
Which is ISO 8601–1:2019 format so our date would be:
2020–10–27T00:00:00.000000
So if you update to use this ‘round trip’ format everything will work.
Here it is as a PowerShell function:
Good luck.
TL:DR This is what happens if you have an invalid date format in your data source for a datetime when using T-SQL’s OPENROWSET or BULK INSERT command.
