How to fix BCP in of DATETIME type data?
The fix required use of a generated format file (format nul -x) and using -f rather than -n on both the export and import. Even though the schema for both the export and the import were identical there was a problem using -n for the DATE data type. The generated format file was untouched; no editing to it at all.
Why does CSV file have the same format as BCP?
The CSV file has the exact same format. The timestamps are identical in format but I keep getting an invalid date format error. I have tried using bcp with -n and a format file generated with -n and a format file generated with -x. All give the invalid date format error.
How to fix BCP in SQL Server Native Client?
A solution would be reformat the date into ISO Standard format (YYYYMMDD) in the data file. Alternatives would be either reformat the date to match the DATEFORMAT of the target database or change the DATEFORMAT by changing the database’s default language setting.
What is the SQL Server Native Client 10.0 invalid date format?
SQLState = 22008, NativeError = 0 Error = [Microsoft] [SQL Server Native Client 10.0]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft] [SQL Server Native Client 10.0]Invalid date format SQLState = 22008, NativeError = 0 Error = [Microsoft] [SQL Server Native Client 10.0]Invalid date format SQLState = 22005, NativeError = 0
How to ensure datetimes are formatted like this?
Ensure your datetimes are formatted like this: It has been a while since I ran into an issue similar to this, but I remember this being a step I took to resolve my problem. In case this helps anyone. I had similar trouble with a native format BCP export/import of a table containing DATE data type (new in SQL Server 2008).
How to upload BCP to a destination table?
I created a staging table on the database with all the same column names as the destination table but with each column type as varchar. Then bcp worked fine using the character option -c to upload into the staging table. Then all it needs is the following SQL code to update the actual table and remove the data from the staging table.