[AccessD] Losing 3ms on date insert?

James Button jamesbutton at blueyonder.co.uk
Wed Nov 21 12:26:11 CST 2018


For that small amount I'd be looking at the internal formats used on the source
& destination systems as well as the way the value is held in the transferring
media 
Probably one of them is not holding enough decimal places (or allowing a big
enough n in  2-n) in the storage of the value, so is rounding 
And, as it is only milliseconds, maybe round to the nearest second would deal
with the visibility of the glitch.
  
The date and time being held, by Excel as in 43424.6666666667
And rounding it up to the 7th place  as in adding  0.00000003 to get
43424.66666667 
May be the reason for the extra 3 milliseconds being indicated
While 43424.66666668 shows 4 milliseconds.

And ... that is the perennial problem with the inexperienced use of computers
Well, that and that the books and teachers rarely mention such things - so the
first you know is when it bites you 
and only the fortunate get to notice it in their work before it becomes a public
humiliation 

That - and the other major pain - people who do NOT check that cash values are
not only shown as being in bankable portions of the currency - but are actually
the same value as is being shown 
e.g. $1 split into thirds is 1/3 + 1/3 +1/3 and for the computer the total of
1/3 + 1/3 +1/3 does NOT = 1.00
So - validation should be dealing with whole cents  when the checking has 0.99
not = 1.00
or the split  of the value needs to have the last portion being the base value
less the sum of the other portions, 
or management have to specify some other process to deal with the many little
bits. 
And the coder should consider if(abs(a-b)<0.000limit) rather than if a=b or if
a-b=0.

JimB




-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Daniel Waters
Sent: Wednesday, November 21, 2018 5:39 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Losing 3ms on date insert?

That is a puzzle - I have no answer.  :-(

Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ryan W
Sent: November 21, 2018 11:33
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Losing 3ms on date insert?

No SQL Server is on a VM... but these are 'set' dates, not using GETDATE()
or DATE() or a function where a tiny time discrepancy like that might show
up.

So the source table has 11/20/2018 16:00 as the date, but the destination
table, after the first two rows gets rolled back by 3ms for some reason.



On Wed, Nov 21, 2018 at 10:54 AM Daniel Waters <df.waters at outlook.com>
wrote:

> Is the Access file and the SQL Server on the same PC?  Or on a PC where
> SQL Server is on a Server?
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Ryan W
> Sent: November 21, 2018 10:47
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Losing 3ms on date insert?
>
> Has anyone seen an issue where:
>
> INSERT INTO FOO SELECT * FROM BAR
>
> Where FOO and BAR both contain a date column, but when inserted in from MS
> Access to SQL the date loses 3ms?
>
>
> The first two rows, proper date.. following rows -3ms ?
>
> https://i.imgur.com/IYsKCbS.png (Screencap)
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list