Failure to use BCP IN to import data into a table

Question

bcp "SELECT * from dp_history21052024 WHERE CONVERT(DATE,effective_dt,103) >'29-Feb-2024'" queryout "D:\Data\Data.csv" -S ORBITLITEDB-SVR -d OrbitLiteLive -U sa -P xxxxxxxx -c -t, -r\n

and then the below for import

bcp OrbitLiteLive.dbo.dp_history in "D:\Data\Data.csv" -S ORBITLITEDB-SVR -U sa -P xxxxxxxx -c -t, -r\n -E -k -q -T -k -F 2 -e -o "D:\Data\error.txt"

Starting copy... 1000 rows sent to SQL Server. Total sent: 1000 1000 rows sent to SQL Server. Total sent: 2000 1000 rows sent to SQL Server. Total sent: 3000 1000 rows sent to SQL Server. Total sent: 4000 1000 rows sent to SQL Server. Total sent: 5000 1000 rows sent to SQL Server. Total sent: 6000 SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format

Answer 1

Invalid date format

As the message says.

How does your source data look like? Does all rows contain valid dates?

Answer 2

When you want help, it is not the best of ideas to just dump an error message. You should also include information of what troubleshooting you have performed, and why you think the operation should succeed.

Generally, as long as you copy from one table in one database to a table in another database with the same schema, this is the best way:

BCP db.dbo.tbl format nul -n -f format.fmt -S servername -T
BCP "Query" queryout  C:\temp\data.bcp -f format.fmt -S Servername -T
BCP otherdb.dbo.tbl in C:\temp\data.bcp -f format.fmt -S OtherServer -T

By creating a format file and using native format, you avoid that things like date are misinterpreted to regional settings and similar.

However, it presumes that the table definitions are identical! If the column order is different, or columns are missing, you will need to edit the format file. (And difference in column order could also be the reason for the errors you are getting now.)

By the way, I took the liberty to edit your post to x out the password, in case it is authentical.

Answer 3

Hi @Godfrey Mugisa

See this doc: Copying Date Values.

bcp uses the ODBC bulk copy API. Therefore, to import date values into SQL Server, bcp uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]).

Have you tried convert the date datatype to recommended datatype?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".