Getting datacopy to correctly copy date and time data-types (for Sybase to Sybase)

Background
Datacopy and bcp currently don’t handle date or time data-types correctly, probably because these data-types were introduced after the Microsoft/Sybase split. Attempts to copy the data using datacopy fail and bcp copies the data as if it were a full datatime with the date or time part missing depending on the source data-type.
It seems something goes wrong in the compatibility exchange so Sybase defaults to sending the data as datetime.
A quick Fix
I propose a change to bulk.c as a quick fix. Sybase tells free TDS that the data is a datetime but it passes a hint that things are not right by setting the correct user types. I therefore use the user types to see what I should really be using and then take the first four bytes, or the last four depending on the user type.
For fixed columns the change is in bulk.c in the function tds_bcp_add_fixed_columns. New lines are in highlight colours.

                if (is_numeric_type(bcpcol->column_type)) {
                        num = (TDS_NUMERIC *) bcpcol->bcp_column_data->data;
                        cpbytes = tds_numeric_bytes_per_prec[num->precision];
                        memcpy(&rowbuffer[row_pos], num->array, cpbytes);
                } else if (bcpcol->column_type == SYBBIT) {
                        /* all bit are collapsed together */
                        if (!bitleft) {
                                bitpos = row_pos++;
                                bitleft = 8;
                                rowbuffer[bitpos] = 0;
                        }
                        if (bcpcol->bcp_column_data->data[0])
                                rowbuffer[bitpos] |= 256 >> bitleft;
                        --bitleft;
                        continue;
                } else if (bcpcol->column_usertype == 37) {
                        bcpcol->column_size = 4;
                        cpbytes = bcpcol->column_size;
                        memcpy(&rowbuffer[row_pos], bcpcol->bcp_column_data->data, bcpcol->column_size);
                } else if (bcpcol->column_usertype == 38) {
                        bcpcol->column_size = 4;
                        cpbytes = bcpcol->column_size;
                        memcpy(&rowbuffer[row_pos], bcpcol->bcp_column_data->data+4, bcpcol->column_size);
                } else {
                        cpbytes = bcpcol->bcp_column_data->datalen > bcpcol->column_size ?
                                  bcpcol->column_size : bcpcol->bcp_column_data->datalen;
                        memcpy(&rowbuffer[row_pos], bcpcol->bcp_column_data->data, cpbytes);

                        /* CHAR data may need padding out to the database length with blanks */
                        /* TODO check binary !!! */
                        if (bcpcol->column_type == SYBCHAR && cpbytes < bcpcol->column_size) {
                                for (j = cpbytes; j <  bcpcol->column_size; j++)
                                        rowbuffer[row_pos + j] = ' ';
                        }
                }

..and also in tds_bcp_add_variable_columns:

                /* move the column buffer into the rowbuffer */
                if (!bcpcol->bcp_column_data->is_null) {
                        if (is_blob_type(bcpcol->column_type)) {
                                cpbytes = 16;
                                bcpcol->column_textpos = row_pos;               /* save for data write */
                        } else if (bcpcol->column_usertype == 38) {
                                cpbytes = 4;
                                memcpy(&rowbuffer[row_pos], bcpcol->bcp_column_data->data+4, cpbytes);
                        } else if (bcpcol->column_usertype == 37) {
                                cpbytes = 4;
                                memcpy(&rowbuffer[row_pos], bcpcol->bcp_column_data->data, cpbytes);
                        } else if (is_numeric_type(bcpcol->column_type)) {
                                TDS_NUMERIC *num = (TDS_NUMERIC *) bcpcol->bcp_column_data->data;
                                cpbytes = tds_numeric_bytes_per_prec[num->precision];
                                memcpy(&rowbuffer[row_pos], num->array, cpbytes);
                        } else {
                                cpbytes = bcpcol->bcp_column_data->datalen > bcpcol->column_size ?
                                bcpcol->column_size : bcpcol->bcp_column_data->datalen;
                                memcpy(&rowbuffer[row_pos], bcpcol->bcp_column_data->data, cpbytes);
                        }
                }

I’ve tested this with both nullable and not nullable columns using ASE 15.7 (SP135 but I don’t think the SP level is significant and in fact I think it should work the same with any currently supported version).
Doing it correctly (as correctly as I can manage).
This is all based on freetds-dev.0.99.220. Sadly this can’t act as a guide for all new data types as SYBDATE and SYBTIME were already partially implemented. However, it’s pretty close!
proto.h
First up is include/freetds/proto.h

/*
 * This file contains defines and structures strictly related to TDS protocol
 */

typedef struct tdsnumeric
{
        unsigned char precision;
        unsigned char scale;
        unsigned char array[33];
} TDS_NUMERIC;

typedef struct tdsoldmoney
{
        TDS_INT mnyhigh;
        TDS_UINT mnylow;
} TDS_OLD_MONEY;

typedef union tdsmoney
{
        TDS_OLD_MONEY tdsoldmoney;
        TDS_INT8 mny;
} TDS_MONEY;

typedef struct tdsmoney4
{
        TDS_INT mny4;
} TDS_MONEY4;

typedef struct tdsdate
{
        TDS_INT dtdays;
} TDS_DATE;

typedef struct tdstime
{
        TDS_INT dttime;
} TDS_TIME;

typedef struct tdsdatetime
{
        TDS_INT dtdays;
        TDS_INT dttime;
} TDS_DATETIME;

So around line 49 I’ve added the two new data types. Here we’re just telling freetds what the data types look like.
convert.h
Next we’ve got include/freetds/convert.h, around line 33:

typedef union conv_result
{
        /* fixed */
        TDS_TINYINT ti;
        TDS_SMALLINT si;
        TDS_USMALLINT usi;
        TDS_INT i;
        TDS_UINT ui;
        TDS_INT8 bi;
        TDS_UINT8 ubi;
        TDS_FLOAT f;
        TDS_REAL r;
        TDS_MONEY m;
        TDS_MONEY4 m4;
        TDS_DATETIME dt;
        TDS_DATETIME4 dt4;
        TDS_DATETIMEALL dta;
        TDS_DATE dtdays;
        TDS_TIME dttime;
        TDS_NUMERIC n;
        TDS_UNIQUE u;

        /* variable */
        TDS_CHAR *c;
        TDS_CHAR *ib;

        /* sized buffer types */
        struct cc_t {
                TDS_CHAR *c;
                TDS_UINT len;
        } cc;
        struct cb_t {
                TDS_CHAR *ib;
                TDS_UINT len;
        } cb;
}
CONV_RESULT;

Here we’re just adding the two new data types to the list of possible result types.
mem.c
Now for the real work, we start with src/tds/mem.c at around line 736:

/*
 * Default capabilities.
 */
#undef REQ
#define SUPPORTED_REQ_CAP(i) \
        REQ(i,LANG) REQ(i,RPC) REQ(i,EVT) REQ(i,MSTMT) REQ(i,BCP) REQ(i,CURSOR) REQ(i,DYNF) \
        REQ(i,MSG) REQ(i,PARAM) REQ(i,DATA_INT1) REQ(i,DATA_INT2) REQ(i,DATA_INT4) REQ(i,DATA_BIT) \
        REQ(i,DATA_CHAR) REQ(i,DATA_VCHAR) REQ(i,DATA_BIN) REQ(i,DATA_VBIN) REQ(i,DATA_MNY8) \
        REQ(i,DATA_MNY4) REQ(i,DATA_DATE8) REQ(i,DATA_DATE4) REQ(i,DATA_FLT4) REQ(i,DATA_FLT8) \
        REQ(i,DATA_NUM) REQ(i,DATA_TEXT) REQ(i,DATA_IMAGE) REQ(i,DATA_DEC) REQ(i,DATA_LCHAR) \
        REQ(i,DATA_LBIN) REQ(i,DATA_INTN) REQ(i,DATA_DATETIMEN) REQ(i,DATA_MONEYN) \
        REQ(i,CSR_PREV) REQ(i,CSR_FIRST) REQ(i,CSR_LAST) REQ(i,CSR_ABS) REQ(i,CSR_REL) \
        REQ(i,CSR_MULTI) REQ(i,CON_INBAND) REQ(i,PROTO_TEXT) REQ(i,PROTO_BULK) \
        REQ(i,DATA_SENSITIVITY) REQ(i,DATA_BOUNDARY) REQ(i,PROTO_DYNPROC) REQ(i,DATA_FLTN) \
        REQ(i,DATA_BITN) REQ(i,DATA_INT8) REQ(i,WIDETABLE) \
        REQ(i,DATA_UINT2) REQ(i,DATA_UINT4) REQ(i,DATA_UINT8) REQ(i,DATA_UINTN) REQ(i,DATA_TIME) REQ(i,DATA_DATE) REQ(i,LARGEIDENT) \
        REQ(i,SRVPKTSIZE)
#define REQ(i,n) |(((TDS_REQ_ ## n / 8) == i)?(1<<(TDS_REQ_ ## n & 7)):0)
#define REQB(i) 0 SUPPORTED_REQ_CAP(i)

This time we’re adding DATE and TIME to the list of capabilities. This has the effect of telling ASE that this program can handle those data types. Without this ASE will send dates and times as datetime.
I believe the positioning is important as it is used to create a bit mask of sorts which will be passed to the ASE server.
query.c
The next step is in src/tds/query.c at around line 770:

        switch (tds_get_conversion_type(curcol->on_server.column_type, curcol->on_server.column_size)) {
        case XSYBCHAR:
        case SYBCHAR:
                fmt = "CHAR(%u)";
                break;
        case SYBVARCHAR:
        case XSYBVARCHAR:
                if (curcol->column_varint_size == 8)
                        fmt = "VARCHAR(MAX)";
                else
                        fmt = "VARCHAR(%u)";
                break;
        case SYBINT1:
                fmt = "TINYINT";
                break;
        case SYBINT2:
                fmt = "SMALLINT";
                break;
        case SYBINT4:
                fmt = "INT";
                break;
        case SYBINT8:
                /* TODO even for Sybase ?? */
                fmt = "BIGINT";
                break;
        case SYBFLT8:
                fmt = "FLOAT";
                break;
        case SYBDATETIME:
                fmt = "DATETIME";
                break;
        case SYBDATE:
                fmt = "DATE";
                break;
        case SYBTIME:
                fmt = "TIME";
                break;
        case SYBBIT:
                fmt = "BIT";
                break;
        case SYBTEXT:
                fmt = "TEXT";
                break;

The addition of these two lines is to tds_get_column_declaration is to make sure freetds understands a column definition which includes DATE and/or TIME.
datacopy.c
Now it’s the turn of src/apps/datacopy.c at or around line 671:

for (col = 0; col < src_numcols; col++) {

                /* Find out if there is an identity column. */
                colinfo.SizeOfStruct = sizeof(colinfo);

                if (dbtablecolinfo(dbsrc, col+1, (DBCOL *) &colinfo) != SUCCEED)
                        return FALSE;
                if (colinfo.Identity)
                        identity_column_exists = TRUE;

                srcdata[col].coltype = dbcoltype(dbsrc, col + 1);
                switch (srcdata[col].coltype) {
                case SYBBIT:
                case SYBINT1:
                case SYBINT2:
                case SYBINT4:
                case SYBINT8:
                case SYBFLT8:
                case SYBREAL:
                case SYBMONEY:
                case SYBMONEY4:
                case SYBDATETIME:
                case SYBDATE:
                case SYBTIME:
                case SYBDATETIME4:
                case SYBCHAR:
                case SYBTEXT:
                case SYBBINARY:
                case SYBIMAGE:
                case SYBNUMERIC:
                case SYBDECIMAL:
                        break;
                default:
                        fprintf(stderr, "Type %d not handled by datacopy\n", srcdata[col].coltype);
                        exit(1);
                }
        }

This is in transfer_data and just adds DATE and TIME to the list of data types supported by datacopy.
convert.c
Finally, the largest amount of work needs to be done in src/tds/convert.c. There are several things to be done. The first I put at around line 1481. This is the definition of tds_convert_time and tds_convert_date to add the ability to convert DATEs and TIMEs:

for (col = 0; col < src_numcols; col++) {

                /* Find out if there is an identity column. */

static TDS_INT
tds_convert_time(const TDSCONTEXT * tds_ctx, const TDS_TIME * dttime, int desttype, CONV_RESULT * cr)
{
        char whole_date_string[64];
        TDSDATEREC when;

        switch (desttype) {
        case TDS_CONVERT_CHAR:
        case CASE_ALL_CHAR:
                tds_datecrack(SYBTIME, dttime, &when);
                tds_strftime(whole_date_string, sizeof(whole_date_string), "%I:%M:%S:%z%p", &when, 3);
                return string_to_result(whole_date_string, cr);
        case CASE_ALL_BINARY:
                return binary_to_result(dttime, sizeof(TDS_TIME), cr);
        case SYBTIME:
                cr->dttime = *dttime;
                return sizeof(TDS_TIME);
        default:
                break;
        }
        return TDS_CONVERT_NOAVAIL;
}

static TDS_INT
tds_convert_date(const TDSCONTEXT * tds_ctx, const TDS_DATE * dtdays, int desttype, CONV_RESULT * cr)
{
        char whole_date_string[64];
        TDSDATEREC when;

        switch (desttype) {
        case TDS_CONVERT_CHAR:
        case CASE_ALL_CHAR:
                tds_datecrack(SYBDATE, dtdays, &when);
                tds_strftime(whole_date_string, sizeof(whole_date_string), "%b %e %Y", &when, 3);
                return string_to_result(whole_date_string, cr);
        case CASE_ALL_BINARY:
                return binary_to_result(dtdays, sizeof(TDS_DATE), cr);
        case SYBDATE:
                cr->dtdays = *dtdays;
                return sizeof(TDS_DATE);
        default:
                break;
        }
        return TDS_CONVERT_NOAVAIL;
}

Please note is that I have only done the absolute minimum here, in that we can manage processing a DATE, for example, which is going to a DATE or a CHAR but not much else. 
I also hard coded the date and time character representation. I did that because there’s no LOCALE defined for just date and times and the more I found in freetds the more it looked like it was only really set-up to handle dates and times in this format. 
Still in, and inside tds_convert:

        case SYBMSTIME:
        case SYBMSDATE:
        case SYBMSDATETIME2:
        case SYBMSDATETIMEOFFSET:
                length = tds_convert_datetimeall(tds_ctx, srctype, (const TDS_DATETIMEALL *) src, desttype, cr);
                break;
        case SYBDATETIME:
                length = tds_convert_datetime(tds_ctx, (const TDS_DATETIME* ) src, desttype, 3, cr);
                break;
        case SYBDATETIME4:
                length = tds_convert_datetime4(tds_ctx, (const TDS_DATETIME4* ) src, desttype, cr);
                break;
        case SYBDATE:
                length = tds_convert_date(tds_ctx, (const TDS_DATE* ) src, desttype, cr);
                break;
        case SYBTIME:
                length = tds_convert_time(tds_ctx, (const TDS_TIME* ) src, desttype, cr);
                break;
        case SYBLONGBINARY:
        case CASE_ALL_BINARY:
                length = tds_convert_binary((const TDS_UCHAR *) src, srclen, desttype, cr);
                break;
        case SYBUNIQUE:
                length = tds_convert_unique(src, desttype, cr);
                break;
        case SYBNVARCHAR:
        case SYBNTEXT:
        default:
                return TDS_CONVERT_NOAVAIL;
                break;
        }

This change was simply to tell tds_convert that we can convert DATEs and TIMEs and to do so it must call the new functions defined above.
Last, but not least, is a change in tds_datecrack

        case SYBMSTIME:
                } else if (datetype == SYBDATETIME) {
                const TDS_DATETIME *dt = (const TDS_DATETIME *) di;
                dt_time = dt->dttime;
                dms = ((dt_time % 300) * 1000 + 150) / 300 * 10000u;
                dt_time = dt_time / 300;
                secs = dt_time % 60;
                dt_time = dt_time / 60;
                dt_days = dt->dtdays;
        } else if (datetype == SYBDATETIME4) {
                const TDS_DATETIME4 *dt4 = (const TDS_DATETIME4 *) di;
                secs = 0;
                dms = 0;
                dt_days = dt4->days;
                dt_time = dt4->minutes;
        } else if (datetype == SYBDATE) {
                const TDS_DATE *dtdays = (const TDS_DATE *) di;
                secs = 0;
                dms = 0;
                dt_days = dtdays->dtdays;
                dt_time = 0;
        } else if (datetype == SYBTIME) {
                const TDS_TIME *dttime = (const TDS_TIME *) di;
                dt_time = dttime->dttime;
                dms = ((dt_time % 300) * 1000 + 150) / 300 * 10000u;
                dt_time = dt_time / 300;
                secs = dt_time % 60;
                dt_time = dt_time / 60;
                dt_days = 0;
        } else
                return TDS_FAIL;

Here we’re adding information about how to “crack” open a DATE or a TIME.
Testing
I’ve tested this on ASE 15.7 with a table containing both dates and times as well as datetime and a few other data types. The tests were for both datacopy and bcp.
I don’t claim that these tests were exhaustive but I think I’ve covered most things.
Any help or corrections would be much appreciated.