Convert 13-digit string to date in MS Access Query

Question

I have received a link to some data with dates in a text column stored as 13-digit numbers in a SHORT TEXT field. I want to display these as dates in an MS Access 2016 database table. The field I want to append them to is a date formatted field.

The dates are in this format (they should all be recent dates):

1533268800000, 1533697200000, 1533783600000, 1534129200000, 1534129200000

Does anyone know how I could convert these strings to dates formatted as this format "25/04/2018 16:13:46"? I've tried some advice from this site, but, nothing has worked in Access so far.

This is my current query, but, it gives me the wrong date in Access, but, the right date in Excel:

UPDATE SH_Interviews_Data 
INNER JOIN SH_Data_addition 
ON SH_Interviews_Data.sel_nombre = SH_Data_addition.id_nombre 
SET SH_Data_addition.cargo = [SH_Interviews_Data].[cargo], 
SH_Data_addition.ultimo_contac1 = [SH_Interviews_Data].[fecha_entrev]/86400000+TimeValue("1970-01-01") ;

Thanks in advance.

Could you explain how those text string are converted to a date ? What is the conversion used ? You haven't given us much to actually work with here? — Minty, Aug 23 '18 at 14:18
Possible duplicate of [In VBA, How to convert a UTC UNIX timestamp to Local timezone Date in a simple way?](https://stackoverflow.com/questions/42120325/in-vba-how-to-convert-a-utc-unix-timestamp-to-local-timezone-date-in-a-simple-w) — Andre, Aug 23 '18 at 14:29

score 1 · Answer 1 · answered Aug 23 '18 at 14:48

There are some very detailed answers in the link Andre provided, but if you want a quick and dirty solution this simple function will take the string and give you a date and time;

Function UnixEpochTime(sInput As String) As Date

    Dim iSeconds As Long
    Dim dStart As Date

    dStart = #1/1/1970#

    iSeconds = Val(sInput / 1000)

    UnixEpochTime = DateAdd("s", iSeconds, dStart)


End Function

DateAdd only deals with whole seconds, hence removing the millisecond portion of the string.

score 0 · Accepted Answer · answered Aug 24 '18 at 12:36

Sorry, I wanted to do this inside of a query. I may not have been clear enough about that. Anyways, this query worked to paste the date into a text field (you could remove CDate from the last line if inputting it into a DATE field):

UPDATE JoinTbl 
INNER JOIN InputTbl 
ON JoinTbl.ID = SH_Data_addition.ID 
SET SH_Data_addition.cargo = [JoinTbl].[cargo], 
SH_Data_addition.ultimo_contact = CDate([JoinTbl].[fecha_entrev]/86400/1000+25569);

Convert 13-digit string to date in MS Access Query

2 Answers2