datetime2 column width

[epa]

Recent versions of MSSQL add a datetime2 type with greater precision than the old datetime. For older clients it is returned as a string, so sqsh can still run queries returning this type. But there's a subtle problem. The type supports fractional seconds with 7 decimal places of precision, but sqsh displays only 6 d.p. So for example

>declare @t datetime2 = sysdatetime() select @t, convert(char(50), @t)
>go -m vert
: 2023-12-04 16:56:51.544554
: 2023-12-04 16:56:51.5445548

By explicitly converting to a wide enough char, you can see the full precision, but sqsh has discarded the last digit. (It appears to have truncated rather than rounded.)

While an approximate result is often good enough, if you wanted to copy and paste into another query you need the full precision, since the truncated value usually won't be equal to the original. For example

>select min(tm) from mytable
>go -m vert
: 2023-11-01 14:36:01.903333
(1 row affected)
>select * from mytable where tm = '2023-11-01 14:36:01.903333'
>go -m vert
(0 rows affected)

Huh, how did I find the date and time but then there were no rows matching it? It's because the datetime2 value was actually one digit longer but sqsh truncated.

Since you show six d.p. currently, displaying one more wouldn't be unreasonable and it would make the output a lot more useful, by showing the exact value.

[epa]

Hmm, on further investigation it may not be quite so straightforward to fix as it appears sqsh does understand the datetime2 type (not just getting it as a string) and the fractional seconds come from the datesecfrac field, which is populated by FreeTDS as microseconds. So although the type has 0.1 microsecond precision, FreeTDS is not passing that down.

Newer FreeTDS releases may have a decimicrosecond field; so let me see if I can build sqsh against that.

[vonloxley]

Haven’t looked at the code in a long time, but I think dsp_datetime_strip is responsible and is used in dsp_conv.c. I’d give it a try. Perhaps try patching line 455 and 461 there first.

I’m reluctant to change the default code for the precision of CS_BIGDATETIME_TYPE, but would consider an env var to specify it, similar to the way length and precision are configurable for floats. Would that meet your requirements?

[epa]

I think it depends what else CS_BIGDATETIME_TYPE is used for. If it's used for other datetimey types (perhaps on Sybase) and those perhaps have only millisecond precision, perhaps it shouldn't change just for the sake of MSSQL.

But if sqsh knows it is definitely fetching the Microsoft datetime2 which is defined to have 7 d.p. of precision, I think it should display that literal value. I can see you might sometimes want to round it to a more friendly-looking display, but truncating from 7 to 6 d.p. is a bit daft.

If there isn't a way for sqsh to know the real precision of the database type, or you're adamant that the default should stay at 6 decimal places, then I think setting the format string will be better than an environment variable. If %q displays the fractional seconds with up to 6 d.p. then a new format %x (or whatever name) should display the fractional seconds with all available precision.

[epa]

It appears that current stable FreeTDS (1.4.10) does not pass through the higher precision to the CS_DATEREC structure. It does this

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

Note that datesecprec gives the precision of the field but sqsh does not use this information.

I think if sqsh took account of datesecprec then FreeTDS could in turn populate the full decimicrosecond value (and set datesecprec to 10^7 instead of 10^6). That might also take care of the controversy about how many d.p. to display. It can display as many as needed for the stated precision.

[vonloxley]

Sounds good. I think that this would need changes to dsp_datetime_conv and dsp_datetime_len. IIRC this would still need you to set the datetime variable or the default server conversion will be used.

[epa]

Drat, I thought I had a fix for this but it turns out the full precision is not reaching that FreeTDS code. When it divides by ten, that's because decimicrosecond is always a multiple of ten. Although the datetime2 type has 7 d.p., somehow this is getting rounded to 6 d.p. before FreeTDS sees it.

[epa]

I suspect this may not be possible. I'll see what the FreeTDS developers say in response to FreeTDS/freetds#519