Converting epoch time to "date/time"

  • Thread starter Thread starter MGFoster
  • Start date Start date
M

MGFoster

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

JET (Access) dbs keep DateTime in Double precision format: Integer is
the day; decimal is the seconds since midnight. A date & time would
look like this (the Access debug window [Ctrl-G]):

? cdbl(cdate("2/2/2004 11:06:07 AM"))
38019.4625810185

Day 1 is 31 Dec 1899. Midnight is .0.

So, take some of the stored dates from the problem db & do this to see
if they are really Access DateTime values:

? format("38019.4625810185", "m/d/yyyy hh:nn:ss")
2/2/2004 11:06:07

Substitute the problem DateTime number for "38019.4625810185".

HTH,

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

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

iQA/AwUBQB6iOIechKqOuFEgEQI7iQCfbmlLfMHHvEU5vSTqYNAw06SWH8gAoNqD
nsPtppkepefCm5goiZLQfRog
=pRb/
-----END PGP SIGNATURE-----
 
Matthew said:
I'm trying to report against a .mdb produced by third-party software.
The software keeps dates in epoch (seconds since midnight on a
particular date (not sure what date, either. Need to contact software
mfr)) format, and I'm looking for a way to convert that to a date/time
stamp.
There doesn't appear to be a way to do this using field formats within
the report. Does anyone have some code for making the conversion? Or
know of a way to trick the report into doing it itself?
Click to expand...

Sounds like they may be Unix type dates. Try this kind of
expression:

DateAdd("s", epochdate, #1/1/1970#)
 
I'm trying to report against a .mdb produced by third-party software.
The software keeps dates in epoch (seconds since midnight on a
particular date (not sure what date, either. Need to contact software
mfr)) format, and I'm looking for a way to convert that to a date/time
stamp.
There doesn't appear to be a way to do this using field formats within
the report. Does anyone have some code for making the conversion? Or
know of a way to trick the report into doing it itself?

I'm using Access 2002.

TIA.

--
Matthew Poole Auckland, New Zealand
"Veni, vidi, velcro...
I came, I saw, I stuck around"

My real e-mail is mattATp00leDOTnet
 
Matthew said:
I take it that "epochdate" is the date stored in the database, and I use
this function as the ControlSource for the field that I'm returning the
date into?
Click to expand...

Yes, that is correct (with an = sign in front of it).

Or, if epochdate is a variable, you could use that in a VBA
expression.

Or, you could even use it in a calculated field in a query.
 
Matthew Poole wrote:
*SNIP*
Sounds like they may be Unix type dates. Try this kind of
expression:

DateAdd("s", epochdate, #1/1/1970#)
Click to expand...
I take it that "epochdate" is the date stored in the database, and I use
this function as the ControlSource for the field that I'm returning the
date into?

--
Matthew Poole Auckland, New Zealand
"Veni, vidi, velcro...
I came, I saw, I stuck around"

My real e-mail is mattATp00leDOTnet
 
i thought *nixes used that for the start of the epoch, but M$ uses
1/1/1980.

Tho I may be wrong again (:

steve
 
On Tue, 03 Feb 2004 00:00:22 -0600, Marshall Barton

i thought *nixes used that for the start of the epoch, but M$ uses
1/1/1980.
Click to expand...
*SNIP*

I thought MS used 1/1/1900, to be honest. And the time figures are
around the 3b mark, which would be about right.
I'll keep 1/1/1980 in mind, though, since the manufacturer of the
software hasn't come back to me on what their epoch date is - I suspect
I'll have to try several possibilities.

--
Matthew Poole Auckland, New Zealand
"Veni, vidi, velcro...
I came, I saw, I stuck around"

My real e-mail is mattATp00leDOTnet
 
Steve said:
i thought *nixes used that for the start of the epoch, but M$ uses
1/1/1980.
Click to expand...

Dates in ***Access*** are Days since 30 Dec 1899.

The base date is easy to determine within whatever system
you're using, just format 0 as a date.
 
Back
Top