[AccessD] Losing 3ms on date insert?

[Stuart McLachlan]

ROTF!

How many digits of precision in a log table? 



On 26 Nov 2018 at 15:15, Jim Lawrence wrote:

> ...And then of course, and I am really dating myself, there is log
> tables and log functions. They were basically used to eliminate the
> need for decimal numbers, multiplying and dividing. No round off or
> number storage errors.
> 
> Jim 
> 
> ----- Original Message -----
> From: "Gustav Brock" <gustav at cactus.dk>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com> Sent: Monday, November 26, 2018 7:21:58
> AM Subject: Re: [AccessD] Losing 3ms on date insert?
> 
> Hi Ryan
> 
> The original value is exactly 2018-11-20 16:00:00.000.
> The gravitation is exactly 5ms.
> 
> It will, when stored in SQL Server using DateTime be rounded to:
> 
> 43424.6666666319
> 
> which is - as you have noted - 3ms off.
> 
> However, the 5ms cannot even be caused by some rounding, so there must
> be something in between, we don't know of.
> 
> /gustav
> 
> -----Oprindelig meddelelse-----
> Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af Ryan
> W Sendt: 26. november 2018 16:01 Til: Access Developers discussion and
> problem solving <accessd at databaseadvisors.com> Emne: Re: [AccessD]
> Losing 3ms on date insert?
> 
> 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