[AccessD] Losing 3ms on date insert?

[Jim Lawrence]

Thanks for the info.

Jim

----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Tuesday, November 27, 2018 2:30:16 AM
Subject: Re: [AccessD] Losing 3ms on date insert?

Hi Jim

It might be simpler - or would at least follow a standard - to convert to Unix time. 
The function here uses Decimal, but values aren't larger than they could be stored as Currency.

<code>
' Returns the Unix Time in seconds for a specified date.
' UtcDate can be any Date value of VBA with a resolution of one millisecond.
'
' Examples:
'    100-01-01 00:00:00.000 ->  -59011459200
'    100-01-01 00:00:00.001 ->  -59011459199.999
'    100-01-01 00:00:00.002 ->  -59011459199.998
'   1899-12-30 00:00:00.000 ->   -2209161600
'   1970-01-01 00:00:00.000 ->             0
'   2018-08-18 03:24:47.000 ->    1534562687
'   2018-08-18 18:24:47.000 ->    1534616687
'   9999-12-31 23:59:59.000 ->  253402300799
'   9999-12-31 23:59:59.998 ->  253402300799.998
'   9999-12-31 23:59:59.999 ->  253402300799.999
'
' 2016-02-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function UnixDate( _
    ByVal UtcDate As Date) _
    As Variant
    
    Dim Result  As Variant
    
    Result = Int((CDec(DateToTimespan(UtcDate) + CDec(UtOffset)) * MillisecondsPerDay + 0.5)) / MillisecondsPerSecond
    
    UnixDate = Result
    
End Function
</code>

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af Jim Lawrence
Sendt: 26. november 2018 23:16
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] Losing 3ms on date insert?

I wonder, if the number could be stored as just a value without a decimal point. Many years ago when working with the AcPac accounting package, I found they never stored decimal numbers because of the round off errors. They would store a number as a whole number and then store a mantissa value. So a number would store like 12345698123 and a mantissa value of 9 and when the value was displayed it showed 12.345698123.

Is that a possible solution?

Also, in my much younger years I used Fortran and it would have number values of a hundred of more decimal places. I do not remember any round off errors. Here is a link to an online Fortran editor: https://www.jdoodle.com/execute-fortran-online 

There is a full open source version of Fortran (http://gcc.gnu.org/fortran/) and its compiler...download it, run it and use it. ;-)

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


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