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

    Question

  • 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">
    <RECORD>
    <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"/>
    </RECORD>
    <ROW>
    <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="6" NAME="high" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="5"/>
    <COLUMN SOURCE="7" NAME="low" 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"/>
    </ROW>
    </BCPFORMAT>

     

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

    Thanks,

    Matthew

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

    • Edited by MatthewAdams Wednesday, July 21, 2010 6:12 PM xml got wacked
    Wednesday, July 21, 2010 5:39 PM

Answers

All replies

  • Is it SQLDATETIM4 ? It shoud be 
    SQLDATETIME right?
    
    Thursday, July 22, 2010 7:59 AM
  • Hi,

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


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, July 23, 2010 8:11 AM
    Moderator
  • The bcp.exe command produced the datetime types.  SQLDATETIM4 is correct.
    Sunday, July 25, 2010 5:37 PM
  • 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.

    Thanks,

    Matthew

    Sunday, July 25, 2010 5:39 PM
  • Hi,

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


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, July 26, 2010 1:58 AM
    Moderator
  • Can you please try with Non-XML format file and check you got the same problem?
    Monday, July 26, 2010 8:16 PM
  • 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.
    Thursday, July 29, 2010 5:48 PM