Pass through full 7 d.p. precision of time, e.g. datetime2 type

[epa]

I am using FreeTDS with sqsh to connect to an MSSQL 2016 server. It supports a datetime2 data type which has fractional seconds with 7 decimal places. I would like to get back all 7 d.p. on the client but values are returned from FreeTDS with only 6 d.p.

(semi-informed speculation follows, since I am not a FreeTDS developer)

Although in ctlib/cs.c there are a couple of lines of code which convert from 7 to 6 d.p.

               daterec->datesecfrac = dr.decimicrosecond / 10u;
               daterec->datesecprec = 1000000;

I believe the real issue is somwhere in tds/convert.c where the SYB5BIGDATETIME format is decoded. It appears that it comes back over the wire with only 6 d.p. Is that so? The code multiplies it by 10 and then divides by 10 later.

Is there anything that can be done? All I can think of is to set TDSVER to an older level so that the server starts returning these values as strings (with full precision).

[freddy77]

We should follow SAP ABI, see https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc20155.1550/html/newfesd/newfesd48.htm.

Said that it does not mean we can't have some way to extend the behaviour somehow and have SQSH use this extension.

But note that the precision of Sybase times (used by cs_dt_crack) are only 6 digits. So probably the changes are a bit more extensive (I suppose we also need to return data in a different format and type).

[epa]

On the one hand the ABI seems to allow for different precisions. The field datesecprec tells you what units are used, so it's extensible to 7 d.p. or to any precision that would fit in a CS_INT.

However, the ABI documentation you link specifies "For CS_BIGDATETIME and CS_BIGTIME, this field is always 10^6". To support the Microsoft-specific datetime2 you would need to add a new value for datetype. A Microsoft shop like mine could run a local patch or configuration option changing the ABI slightly, but you wouldn't want to take that upstream.

The more important question is whether the higher precision is even available to FreeTDS. If the TDS protocol itself is only sending down six decimal places, there's nothing to be done.

[freddy77]

Obviously the TDS protocol supports all digits. libTDS (the lower level library) uses a structure which is able to handle all precisions (that why it uses deci-microseconds that is 7 digits). But ctlib specifications are written for Sybase which support at maximum 6 digits precision, that's why the /10 in the code. Note that the types here (like CS_BIGTIME) are returned to SQSH using ct_describe, which, respecting Sybase use Sybase types (and Sybase data format, that is datetime2 is converted to bigdatetime loosing the precision).

One easy way would be to have an option in ctlib to return datetime2 as varchar. In that way the format would be done all in FreeTDS and you won't even need to change a line in SQSH. You obviously loose the ability to format the date in SQSH.

[epa]

Thanks, on revisiting tds/convert.c I see I was looking at the wrong section. The SYB5BIGDATETIME case is for Sybase/SAP. The case with MSSQL is SYBMSDATETIME2 and that does set the decimicroseconds (dms) with full precision. However, my attempts to get this passed through to ctlib ended up with always getting a multiple of ten, although I thought I had fixed all the modulo-ten and times-ten in the code. I will need to try again and debug what's happening.

Yes, passing as a string is an easy way out. When using Perl's DBD::Sybase you always get full precision for datetime2 as they are just strings.