[AccessD] Losing 3ms on date insert?

Ryan W wrwehler at gmail.com
Mon Nov 26 09:00:40 CST 2018


Ok looking at this again the dates being stored in excel are start off as:
43424.6666666667


but then gravitate to:

43424.6666666088

I'm not sure I understand why though, they're all being input as 11/20/2018
16:00 and then copied down the row.





On Wed, Nov 21, 2018 at 12:27 PM James Button via AccessD <
accessd at databaseadvisors.com> wrote:

> 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
>
> --
> 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