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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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