ODBC timestamps are off by few milliseconds

Here's a curious test that's failing in 95% of the runs here against SQL
Server 2005 via ODBC,

testTimestamp
 | timestamp |
 timestamp := Timestamp now.
 self
  executeSQL: 'INSERT TestTable (TimestampCol) VALUES (?)'
  parameters: (Array with: timestamp).
 self assert: (self executeSQL: 'SELECT TimestampCol FROM TestTable') first
first = timestamp.
 self executeSQL: 'DELETE TestTable'

One:
 June 29, 2006 16:01:25.013 (insert)
 June 29, 2006 16:01:25.014 (select)

Two:
 June 29, 2006 16:03:42.535 (insert)
 June 29, 2006 16:03:42.537 (select)

Three:
 June 29, 2006 16:04:19.966 (insert)
 June 29, 2006 16:04:19.967 (select)

The table is created in the setUp via,

self executeSQL:
                'CREATE TABLE TestTable
                ( LongStringCol VARCHAR(500)  NULL,
                        IntCol int  NULL,
                        BigIntCol bigint  NULL,
                        TimestampCol datetime  NULL,
                        DateCol datetime  NULL,
                        BooleanCol bit  NULL DEFAULT  0
                ) ON [PRIMARY]'

Has anyone else seen this happen?

Cheers!

-Boris

--
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[hidden email]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

One possibility could be that the fractional part of the second is not
stored in increments of 1 ms but some other 'weird' increment.

If that is so not all millisecond values can be represented exactly and
get rounded to whatever the SQL Server increment is and you get
conversion errors when round tripping the timestamp from St to SQL and
back again.


just a thought, don't know if this is what is going on.

Reinout
-------



Boris Popov wrote:

Looks like Reinout's right as usual: see the Precision section here:
http://www.dbazine.com/db2/db2-disarticles/pelzer2
"Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of three-hundredths of a second, or 3.33 milliseconds"

Steve
and SQL
> > Server 2005 via ODBC,
> >
> > testTimestamp
> >  | timestamp |
> >  timestamp := Timestamp now.
> >  self
> >   executeSQL: 'INSERT TestTable (TimestampCol) VALUES (?)'
> >   parameters: (Array with: timestamp).
> >  self assert: (self executeSQL: 'SELECT TimestampCol FROM
TestTable')

D'oh!

Thanks, Steve and Reinout,

-Boris

--
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[hidden email]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-----Original Message-----
From: Steven Kelly [mailto:[hidden email]]
Sent: Friday, June 30, 2006 3:19 AM
To: VW NC
Subject: RE: ODBC timestamps are off by few milliseconds

Looks like Reinout's right as usual: see the Precision section here:
http://www.dbazine.com/db2/db2-disarticles/pelzer2
"Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of three-hundredths of a second, or 3.33 milliseconds"

Steve
TestTable')