bcp in of datetime type data results in an “Invalid date / time format”

Question

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?

Answer 1

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.