Search code examples
ms-accessmilliseconds

conversion of java milliseconds to readable time in Access db

How can I update a column in a table in an access db, so the time isn't given in java milliseconds (e.g. change "1395751603796" to "25.03.2014 12:46" or something similar)?

Edit for clarification: The type of the column is Text, but I can create a new column of the type Date/Time. I imported values for further use (e.g. grouping by month etc.), and the values just happened to be milliseconds. I'd prefer to just update the table (with > 400,000 rows) in Access.

Solution

  • For a table named [msTable] with a Text field named [milliseconds] and a Date/Time field named [DateTimeField]

    ID  milliseconds   DateTimeField
--  -------------  -------------------
 1  1395751603796

    the query

    UPDATE msTable 
SET DateTimeField = DateAdd("s",Round(CDbl(milliseconds)/1000,0),DateSerial(1970,1,1))

    results in

    ID  milliseconds   DateTimeField
--  -------------  -------------------
 1  1395751603796  2014-03-25 12:46:44