Error in conversion of date using bulk_insert/BCP

[npezolano]

It seems bulk_insert is not able to convert Python str or int properly internally into MSSQL datetime format.

See the example below:

My table schema

CREATE TABLE TestTable (
SID int,
TradeDate date)

Example code:

connection = ctds.connect(HOST)
with connection:
    connection.bulk_insert('TestTable', data)

When data = [(2,'2017-11-01' )] I get following error:

ProgrammingError: Conversion failed when converting date and/or time from character string.

I'm able to insert this data using the freebcp command line and a single integer column data = [(1,)] as well. I've also tried using "20171101" and 20170101 as dates as well. What's the proper way to bulk_insert dates using ctds?

[joshuahlang]

Passing a Python date object should work. E.g. data = [(2, date(2017, 11, 01))]

There is a test which sort of illustrates this (for DATETIME) here.

[npezolano]

That's not working for me as well using bulk_insert still, is this a TDS version problem? I'm on ctds 1.5.0 installed via pip and freetds_version freetds v0.91

In [23]: data = [(2,datetime.date(2017, 11, 1 ))]

In [24]: connection = ctds.connect(HOST)
    ...: with connection:
    ...:     connection.bulk_insert('TestTable', data)
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-24-b0ac229857ee> in <module>()
      2 connection = ctds.connect(HOST)
      3 with connection:
----> 4     connection.bulk_insert('TestTable', data)

ProgrammingError: Conversion failed when converting date and/or time from character string.

[npezolano]

Ok the problem here is the mssql type DATETIME works as you suggested but DATE does not.

[joshuahlang]

Strange. Using date work with FreeTDS 0.95.95+ (just verified it). I suspect there is some issue with the 0.91 version of FreeTDS in this case. It might be related to using an older TDS protocol version (7.1 is the latest supported by 0.91 as I recall), but I think that is unlikely as ctds internally typically converts Python date objects to SQL DATETIME.

[npezolano]

I just checked with explicitly setting export TDSVER=7.1 before hand and same error, it still seems very strange that I'm able to use the freebcp command line and not this.

[joshuahlang]

Yeah, ctds will use the latest supported by whatever version of FreeTDS it is built against, so not specifying it would be the same. Are you able to update to a newer version of FreeTDS?

[joshuahlang]

Technically speaking, freebcp is using a different set of FreeTDS APIs than ctds. freebcp is using bcp_init specifying a data file, where-as ctds does not specify a source data file and instead passes data via bcp_bind. This likely explains the difference in behavior.

[npezolano]

Thank you for the help with identifying what my issue was, this definitely isn't a problem with ctds but with FreeTDS. 0.91 is the default version on Ubuntu 16.04 which is a pain but installing the latest from source is the best option here, thanks again.