[AccessD] Losing 3ms on date insert?

[Gustav Brock]

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)