Sql-server – bcp in of datetime type data results in an “Invalid date / time format”

bcpsql serversql-server-2008

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?

Best Answer

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.

Related Question