Home » C++ Programming

bcp in using datetime with no seconds fails: Invalid character value for cast specification

Hi all,

SQL Server 2008 R2 on Windows 7 Ultimate.

I'm trying to bcp in rows from a CSV file that look like the following:


XYZ,ABC,2009-06-03 18:01,4,1.4157,1.4157,1.4157,1.4157,0


Notice that the third element is a date in the form "yyyy-MM-dd hh:mm".  This causes bcp to err out with the following error:


Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1


If I append ":00" to the datetime in the csv file, the bcp works fine.

I've tried with both datetime and smalldatetime in the target table's column corresponding to that column in the csv file.  I have too much data to go through each row of each csv file and add the ":00" to the datetimes.

QUESTION:  How do I specify in the format file or in the table that my datetimes are coming in the format "yyyy-MM-dd hh:mm"?

I had bcp generate a format file for the table using the command


bcp MyDatabase.dbo.Raw format nul -fformat.xml -Slocalhost\MYDB -c -T -t, -x


which generated this format file:


<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="41"/>
<COLUMN SOURCE="1" NAME="symbol" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="discriminator" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="3" NAME="datetime" xsi:type="SQLDATETIM4"/>
<COLUMN SOURCE="4" NAME="unknown" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="openx" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="5"/>
<COLUMN SOURCE="8" NAME="closex" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="5"/>
<COLUMN SOURCE="9" NAME="volume" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="5"/>


Newbie-level help is appreciated, and I'm under the gun right now.



Note:  I just discovered that this error does not present itself when using the sql BULK INSERT statement.


7 Answers Found


Answer 1

Is it SQLDATETIM4 ? It shoud be SQLDATETIME right?

Answer 2


If you want to bulk import character  data from a data file to a datetime  column of  an instance of sql  Server, the data stored in the data file require to be in the SQL server  native format. However,  yyyy-MM-dd hh:mm is a invalid  format for smalldatetime.

To work around the issue, you could add a character column in the table , import the smalldatetime to the new column,   and then transfer the data to the smalldatetime column with the convert method.

If you have any more questions, please let me know.


Answer 3

The bcp.exe command produced the datetime  types.  SQLDATETIM4 is correct.

Answer 4

As I noted above, if I run the sql  command BULK INSERT, the files get imported correctly.  The problem only shows up when using bcp.exe.  My workaround is to use sqlcmd.exe to execute the BULK INSERT command.

It appears to be a bug in bcp.exe.  I have too many files and too much data to change the datetime  format.




Answer 5


You may consider to submit this issue on the feedback site(https://connect.microsoft.com)with the details. The product team will look for it.

If you have any more questions, please let me know.


Answer 6

Can you please try with Non-XML format file and check you got the same problem?

Answer 7

Sorry, didn't see this and don't have time since I found a workaround and am on to subsequent tasks.  Should be easy enough to duplicate, though.


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure