Convert UNIX time to windows general date

[warren50]

I'm importing a table from a MYSQL database that contains a UNIX time stamp
data field. I need a function or a formula I can use in a query to convert
the UNIX time to the Access general date format.

 

[MGFoster]

I'm importing a table from a MYSQL database that contains a UNIX time stamp
data field. I need a function or a formula I can use in a query to convert
the UNIX time to the Access general date format.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The following is a function I use to convert Unix to Access dates:

Function convUnixDate(dblUnixDate As Double) As Date
' Purpose:
' Convert the Unix date (seconds) to date/time data type
' In:
' dblUnixDate Seconds since Jan 1, 1970 00:00:00
' Use a Double data type 'cuz it's range is:
' 4.94065645841247 E–324 to
' 1.79769313486231 E308
' Don't use any decimal places.
' Out:
' Date/Time
' Created:
' mgf 23oct2004 Converted from web site:
' http://www.experts-exchange.com/
' Databases/Microsoft_SQL_Server/
' Q_20655264.html
' Comment from rdmjrb, Date: 06/24/2003 07:58AM PDT
'
' ALTER FUNCTION convUnixDate (@unixDate BIGINT)
' RETURNS VARCHAR(24)
' AS
' Begin
' DECLARE @tmpDate datetime,
' @DELIMITER integer
' SET @DELIMITER = 2000000000 -- 2,000,000,000
' SET @tmpDate='1970-01-01 00:00'
' WHILE @unixDate > @DELIMITER
' Begin
' SET @tmpDate=DATEADD(SECOND, @DELIMITER, @tmpDate)
' SET @unixDate=@unixDate-@DELIMITER
' End
' SET @tmpDate=DATEADD(SECOND, @unixDate, @tmpDate)
'
' RETURN CONVERT(CHAR(11), @tmpDATE, 101) +
' SUBSTRING(CONVERT(CHAR(20), @tmpDATE, 100), 13, 7)
' END --FUNCTION
'
' Modified:
' mgf 23oct2004 Converted to VBA

Const lngLimit As Long = 2000000000

Dim dteTemp As Date
dteTemp = CDate("1970-01-01 00:00:00") ' Unix start time

Do While dblUnixDate > lngLimit
dteTemp = DateAdd("s", lngLimit, dteTemp)
dblUnixDate = dblUnixDate - lngLimit
Loop

convUnixDate = DateAdd("s", dblUnixDate, dteTemp)

End Function
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwzl1IechKqOuFEgEQKg8QCfes55Qe3DV+/Daq5Bla4VA92YKaoAoIe2
rSBeC0UlDnjeVQC9kqEiHWrG
=gCkY
-----END PGP SIGNATURE-----

 

[warren50]

Thank You,

I understand the function and it looks like it will do exactly what I need.
Unfortunately I'm still using Macro programming instead of VBA. I geuss I'll
be cracking open the "Access 2000 VBA Handbook" I got last year and try to
figure out how to define a public function within a module (i think) and then
how to call that function from my query.

I took 2 years of C++, but I've never ventured into using VB within Access.

 

[John Spencer (MVP)]

You might be able to just use

DateAdd("s",UnixDate,#1/1/1970#)

That assumes that UnixDate is a count of the number of seconds.

 

[warren50]

The most simple answeris the one right under your nose. I really appreciate
everyone's answers to this question,but John's answer is by far the best
solution and has put a great big smile on my face tonight.

with your answer i can continue to live in the stone ages with macro
programming and use the DateAdd function already available. It makes going
into work tomorrow stress free because I have the answer I was looking for.

Thank You!