Time Conversion

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

Guest

Hello -

I have a query in which the table I am pulling in has a field that describes
the time in a large amount of seconds.

For Example, 1143417600 = 12:00 AM 3/27/2006

I use the above as my baseline, since that's what I know to be true.

I need to find a way in a query, to calculate the date and time, given a
number like: 1143447569 (which happens to work itself out to 8:20 AM
3/27/2006)

I have thought of a few ways to do it, but both require a ton of
manipulation (mid, left, functions all over the place). There's got to be an
easier way....I was hoping someone knew what that was???

Thank you so much!!

Nick
 
Important question: what would 0 be for the time and date?

Debug.Print DateAdd("s",-1143417600,CDate("3/27/2006 12:00 AM") ) = 1/1/70

Humm. I've seen that date somewhere before.... Anyway:

DateAdd("s",1143417600,#1/1/1970#)
 
Nick, there are certainly functions in Access to convert something that looks
like a date or date serial to a date (see CDate and CVDate in the Access
help), however I'm not sure if either of these functions will work with what
you've got.

The biggest problem I see with what you have is that you have a number of
seconds that you want to get a date and time from. The question is, if
you're talking about 1143417600 seconds, what are you measuring from (i.e.
1143417600 seconds from when)?

It's entirely possible that someone has already written a function to do
exactly what you need to do, however I'm not sure that it's a common need, so
this may or may not be the case. I would check The Access Web
(http://www.mvps.org/access/ ) to see if they have what you need.

I hope that helps.

-Chris
 
Yeah, the problem is I don't know at what time 0 seconds is equal to. ALl I
know is

1143417600 = 12:00AM on 3/27/2006

Take 1143417600 seconds minus 12:00AM on 3/27/2006 and that's what 0 seconds
is equal to.
 
Since you didn't issue us a Secret Decoder Ring, we don't really know what
the digits mean.

Can you subtract one from the other to get days, then add that number to the
3/27/06 12am date?
 
Here's your secret decoder ring:
0 seconds is equal to 1/1/1970 at 12:00 AM

Knowing this information, how do I exact a date/time from a field from just
a large number of seconds (Ex. 1143590400)? THere must be a semi-easy way to
do this.

Thanks,

Nick
 
As mentioned elsewhere in this thread. Use the DateAdd function.

DateAdd("s",[YourFieldWithSeconds],#1/1/1970#)

You can use that formula whereever you need it.

If you want you could use it in an update query that would populate an
Access DateTime field. This could be useful if your data was stable and the
field with the seconds didn't change and you needed to do date range
searches and groupings.
 
Sorry, I got head-faked when you started talking about using Left & Mid. I
thought that you were getting a string value, and were having to pick off
the parts.

mah baad.
 
Back
Top