Convert from Unix time to Access Date/Time?

Hi all,

I've just imported a large table that is Unix time for the dates, so the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9370-convert-unix-time-access-date-time.html
fields are formatted as "Number" instead of "Date/Time".

For example, May 22, 2006 is 1148264725. I understand this is the number of
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
seconds from January 1, 1970. Are there any functions that can convert this
to a proper date/time?



*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

On Wed, 10 May 2006 17:19:50 -0700, "Marty Christion"
wrote:

>Hi all,
>
>I've just imported a large table that is Unix time for the dates, so the
>fields are formatted as "Number" instead of "Date/Time".
>
>For example, May 22, 2006 is 1148264725. I understand this is the number of
>seconds from January 1, 1970. Are there any functions that can convert this
>to a proper date/time?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
>

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

Testing it out:

?dateadd("s", 1148264725, #1/1/1970#)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
5/22/2006 2:25:25 AM

John W. Vinson[MVP]

Thanks John!

I'm kind of an Access noob, but I was able to use that equation in a query.
If anyone else has the same problem, here's what I did.

I created a query, and added all the fields I needed from the table,
including the Unix "date" field (unix_date).
In an empty column in the "design" view of the query, I entered this
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
equation in the "Field" field:

AccessDate: DateAdd("s",[unix_date],#1/1/1970 5:00:00 AM#)

Now when I run the query, I have a new field called AccessDate which shows
the correct Date/Time. I can use this for forms and reports as needed.




AccessDate: DateAdd("s",[problem_reportdate],#1/1/1970 5:00:00 AM#)
"John Vinson" wrote in message
news:rmd56252nlgfvslc7pkkf88epdkbr6il21@4ax.com...
> On Wed, 10 May 2006 17:19:50 -0700, "Marty Christion"
> wrote:
>
>>Hi all,
>>
>>I've just imported a large table that is Unix time for the dates, so the
>>fields are formatted as "Number" instead of "Date/Time".
>>
>>For example, May 22, 2006 is 1148264725. I understand this is the number
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
>>of
>>seconds from January 1, 1970. Are there any functions that can convert
>>this
>>to a proper date/time?
>>
>
> DateAdd("s", [UnixTime], #1/1/1970#)
>
> Testing it out:
>
> ?dateadd("s", 1148264725, #1/1/1970#)
> 5/22/2006 2:25:25 AM
>
> John W. Vinson[MVP]

On Thu, 11 May 2006 0914 -0700, "Marty Christion"
wrote:

>Thanks John!
>
>I'm kind of an Access noob, but I was able to use that equation in a query.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
>If anyone else has the same problem, here's what I did.
>
>I created a query, and added all the fields I needed from the table,
>including the Unix "date" field (unix_date).
>In an empty column in the "design" view of the query, I entered this
>equation in the "Field" field:
>
>AccessDate: DateAdd("s",[unix_date],#1/1/1970 5:00:00 AM#)
>
>Now when I run the query, I have a new field called AccessDate which shows
>the correct Date/Time. I can use this for forms and reports as needed.

If you wish, you can add a new Date/Time field to the table, and run
an Update query updating it to this expression. Unless you're
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9370
routinely importing data from the Unix file, this may make your forms
and reports run faster since you won't need to call a function on
every row.

John W. Vinson[MVP]