Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited.

I then bcp in to a truncated table with the same columns/format as the original table, however, I obtain errors like:

----------------
Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time 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 = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (266.67 rows per sec.)
----------------

And for the rows that do get copied, the other columns contain garbage data.

If I do the bcp out/in with data in other datatypes, everything seems okay?

How can I make this to work?

share|improve this question
    
Can we have some samples of data please? –  gbn Mar 1 '11 at 20:52
    
I think I had this problem too and the BCP was treating the export as datetime2 ~ what happens if you cast to varchar with a custom export statement instead? –  jcolebrand Mar 1 '11 at 23:37
    
Check the parameters of the bcp utility please, there should be surely some help there. Or verify the possible parameters for Bulk Insert command. I'm sure it's a configuration issue. –  Marian Mar 3 '11 at 0:09
    
@Marian ~ It's something about the datetime format in 2008. I had the same problem, I ended up using the GUI version of the app from SSMS to solve it here. –  jcolebrand Mar 3 '11 at 2:13

3 Answers 3

I have seen this before. Ensure your datetimes are formatted like this:

yyyy-mm-ddThh:mm:ss

Example:

2011-01-18T17:30:59 would represent January 18th 2011 at 5:30:59PM

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.

share|improve this answer

This problem can also happen if the schema of the exporting database does not match the schema of the importing database. For example, on the table where this error occurred for me, the import version of the table had two bit columns added at the end. This was sufficient to corrupt the import process and resulted in this date format error. The solution of course is to ensure that the table definition is identical on both sides.

share|improve this answer

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). 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.