Convert from Milliseconds

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an external datasource that provides me the time in Milliseconds. I want to convert it to a date

Example 1056628832000 is the data provided in milliseconds and I want to convert it to whatever date that represents. I can do it in Javascript but I can't figure out how to do it in Access, I assume I need to use a function but none of the ones I tried worked.
 
rlietzke said:
I have an external datasource that provides me the time in
Milliseconds. I want to convert it to a date.

Example 1056628832000 is the data provided in milliseconds and I want
to convert it to whatever date that represents. I can do it in
Javascript but I can't figure out how to do it in Access, I assume I
need to use a function but none of the ones I tried worked.
Click to expand...

Where does this millisecond count come from? What is the zero point?
 
It comes from an inventory database. Looks like 1/1/1970. Here is the javascript that works
<script language="JavaScript"><!-
var aDate = 1056637868000; // miliseconds Since 1970

theDate = new Date(aDate)
dd = theDate.toUTCString()

//--></script>
 
rlietzke said:
It comes from an inventory database. Looks like 1/1/1970. Here is
the javascript that works. <script language="JavaScript"><!--
var aDate = 1056637868000; // miliseconds Since 1970;

theDate = new Date(aDate);
dd = theDate.toUTCString();


//--></script>
Click to expand...


Access uses an earlier zero-point (midnight, 12/30/1899) and its
date-time values aren't precise beyond the second. Judging by your
examples, though, your millisecond values are really only being sampled
at the level of seconds -- that is, they seem to end in 000. I'm
guessing that for your application we can just divide by 1000 to get a
count of seconds.

In that case, we should be able to transform such a value to an Access
date like this:

Dim dblDateIn As Double
Dim dtDateOut As Date

dblDateIn = 1056637868000

dtDateOut = DateAdd("s", dblDateIn / 1000, #1/1/1970#)
 
I've written a query in which one of the columns is the string, called LastScanDate. You are correct, eac value ends in 000.

I am sorry but I don't understand your answer, I cut and paste you solution into the epression bulder but get an error.
 
rlietzke said:
I've written a query in which one of the columns is the string,
called LastScanDate. You are correct, eac value ends in 000.

I am sorry but I don't understand your answer, I cut and paste you
solution into the epression bulder but get an error.
Click to expand...

The expression builder can't represent a series of VBA statements. I
think I would write a function to encapsulate this conversion, and call
that function in the definition of a calculated field in the query. For
example, you could copy and paste the following function into a standard
module in your database:

'---- start of function code ----
Public Function ScanDateToDate(ScanDate As Variant) As Variant

If IsNumeric(ScanDate) Then
ScanDateToDate = DateAdd("s", CDbl(ScanDate) / 1000, #1/1/1970#)
End If

End Function
'---- end of function code ----

Then you would define a calculated field in your query like this:

LastScanned: ScanDateToDate([LastScanDate])

If you looked at the query in SQL View, it would look something like
this example:

SELECT
SomeField,
SomeOtherField,
ScanDateToDate([LastScanDate]) As LastScanned
FROM
SomeTable;
 
Back
Top