[egenix-users] "right truncation" warning in mx.ODBC.Windows

[Steve Holden]

[Jim Vickroy]
> I too, thought I reported this problem some time in the
> **distant** past,
> but maybe I am mistaken <smile>.
>
> Anyway, MS SQL Server has a time resolution of 3.33
> milliseconds; see for
> example:
>
> http://www.databasejournal.com/features/mssql/article.php/1442
> 361#part_4
>
> My experience has been that trying to press that limit still triggers
> occassional warnings; truncating mx.DateTime objects to
> centi-seconds for
> insertion in MS SQL Server definitely works with the two
> versions (6, 7) I
> have encountered.
>
More information which I forwarded to Marc-Andre earlier but omitted to
copy to this list ...

[mal]
> Thanks for checking. Unless we find a better alternative, I
> guess rounding to the nearest quarter of a second is the
> way to go... or maybe we can just silence the warning in this
> particular case.
>
Hold on! I have just run a program to transfer a table from one copy of
SQL Server 2000 to another (Enterprise to Personal, if it matters, I
don't know).

Table definition is the same on both machines ... I had Enterprise
manager script them both to verify this.

I find that the sending system (version 8.00.534 (SP2)) is producing
datetime values by defaulting the column in question to the value
GETDATE().

The receiving instance of SQL Server 2000 (version 8.00.760 (SP3)) can't
store these datetimes because of the fractional values!

Here's the list of values I see from the sending instance in SQL Squery
Analyzer:

2004-03-08 14:43:31.647
2004-03-08 15:19:25.853
2004-03-08 15:21:03.847
2004-03-08 15:22:11.483
2004-03-08 15:27:14.830
2004-03-08 15:30:57.210
2004-03-08 15:33:52.070
2004-03-08 15:38:47.587
2004-03-09 10:29:56.187
2004-03-09 10:31:01.040
2004-03-09 10:31:48.677
2004-03-09 10:37:22.677
2004-03-10 10:40:00.060
2004-03-11 10:49:12.030
2004-03-12 10:05:17.170

Clearly there is some granularity there, but just as clearly it isn't
0.25 seconds. I have no idea what gives here, as there don't appear to
be any specific option settings to control this behavior.

When I print the datetime objects from the read of this table I see:

(2, <DateTime object for '2004-03-08 14:43:31.64' at 7c3b70>,
(3, <DateTime object for '2004-03-08 15:19:25.85' at 7c3bf0>,
(4, <DateTime object for '2004-03-08 15:21:03.84' at 7c3c70>,
(5, <DateTime object for '2004-03-08 15:22:11.48' at 7c3cf0>,
(6, <DateTime object for '2004-03-08 15:27:14.83' at 7c3d70>,
(7, <DateTime object for '2004-03-08 15:30:57.21' at 7c3df0>,
(8, <DateTime object for '2004-03-08 15:33:52.07' at 7c3e70>,
(9, <DateTime object for '2004-03-08 15:38:47.58' at 7c3f38>,
(10, <DateTime object for '2004-03-09 10:29:56.18' at 7c3fb8>,
(11, <DateTime object for '2004-03-09 10:31:01.04' at 7c4040>,
(12, <DateTime object for '2004-03-09 10:31:48.67' at 7c40c0>,
(13, <DateTime object for '2004-03-09 10:37:22.67' at 7c4140>,
(14, <DateTime object for '2004-03-10 10:40:00.06' at 7c41c0>,
(15, <DateTime object for '2004-03-11 10:49:12.03' at 7c4240>,
(16, <DateTime object for '2004-03-12 10:05:17.17' at 7c42c0>,

So, it could be that the problem is truncation on import into DateTime
rather than anything else. Don't know whether this will help, but it's
another data point at least.

regards
--
Steve Holden                                 http://www.holdenweb.com/
ReportLab Enterprise Publishing Solutions    http://www.reportlab.com/
Chairman, PyCON DC 2004                          http://www.pycon.org/
Telephone: +1-800 494 3119                        Fax: +1 703 278 8289