asked on

MSSQL Bulk Copy issue for DateTime columns

while trying to use BCP of SQL Server (https://docs.microsoft.com/en-us/sql/connect/jdbc/using-bulk-copy-with-the-jdbc-driver?view=sql-server-2017)

one issue we are facing here is it is giving the below error for datetime field , eventhough the data is proper

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
      at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
      at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)
      at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:28)
      at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1611)
      at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$200(SQLServerBulkCopy.java:58)
      at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:709)
      at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)

Data giving the error

      <header>
             <id>1</Account_ID>
             <name>NULL</name>
             <firstName>NULL</firstName>
             <middleName>NULL</middleName>
             <lastName>NULL</lastName>
             <idType>AAA</idType>
             <excemptionLevel>NULL</excemptionLevel>
             <riskRating>NULL</riskRating>
             <isMinor>NULL</isMinor>
             <isResident>NULL</isResident>
             <openDate>2004-05-23T14:25:10.487</openDate>
             <closeDate>2004-05-23T14:25:10.487</closeDate>
             <desc />
             <shortDesc>NULL</shortDesc>
             <bankId>pace</bankId>
             <branchId>404</branchId>
      </header>

As per this issue https://github.com/Microsoft/mssql-jdbc/issues/705 we made the precision,scale changes as well, but still the error perists.

Any help will be appreciated..

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

when you're doing the data importing, you may probably want to convert your field: openDate to DateTime using Cast function, try like:

;with yourTable as
(
	select '2004-05-23T14:25:10.487' openDate
)
Select openDate, cast(openDate as datetime) openDate2
from yourTable

Open in new window

Dirk Strauss

Ensure that the field you are adding the date value to is in fact DATETIME and that the value is being correctly converted to DateTime. For example:

DECLARE @dteFld VARCHAR(50)
SET @dteFld = '2004-05-23T14:25:10.487'

SELECT CONVERT(DATETIME, @dteFld) AS 'Date Format'

Open in new window

Jacob Glassman

ASKER

Thank you all,

Sorry for the late reply

the reason was as @lcohan mentioned source columnlist and destination columnlist was not matching. Even though we handled that in the code somehow BCP was inserting data in the wrong order.

In order to handle that we explicitly mapped the source column name and destination column name in the code

try(SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connectionUrl)){
bulkCopy.addColumnMapping(metadata.getColumnName(i), metadata.getColumnName(i));
}

bulkCopy.writeToServer(record);