Covert UTC date
 

I currently have a field coming out of a database with the call_time is
10 digit UTC format (simply the number of seconds that have passed from
that moment since 1970. I have converted it in Brio, but am not that
savy with VBA or Excel. Does anyone know how I can do this? I have
attached the script I use in Brio below:

CONVERT(VARCHAR(50),DATEADD(SS, (CALL_TIME + (1799-(((CALL_TIME %
86400)% 1800)))) - DATEDIFF(HH, GETDATE(),
GETUTCDATE()) * 3600, 'JAN
01, 1970 12:00AM'),114)

If anyone can help, I would REALLY appreciate it!!

Shawn.


--
smpayne69
------------------------------------------------------------------------
smpayne69's Profile: http://www.excelforum.com/member.php...o&userid=29307
View this thread: http://www.excelforum.com/showthread...hreadid=490251

Covert UTC date
 

=(C1/86400)+DATE(1970,1,1)

or in VBA

(assume your value is in the variable lngUTC)

Dim dt as Date
dt = lngUTC/86400 + DateSerial(1970,1,1)

msgbox format(dt,"mm/dd/yyyy hh:mm")


--
Regards,
Tom Ogilvy



"smpayne69" wrote
in message ...

I currently have a field coming out of a database with the call_time is
10 digit UTC format (simply the number of seconds that have passed from
that moment since 1970. I have converted it in Brio, but am not that
savy with VBA or Excel. Does anyone know how I can do this? I have
attached the script I use in Brio below:

CONVERT(VARCHAR(50),DATEADD(SS, (CALL_TIME + (1799-(((CALL_TIME %
86400)% 1800)))) - DATEDIFF(HH, GETDATE(),
GETUTCDATE()) * 3600, 'JAN
01, 1970 12:00AM'),114)

If anyone can help, I would REALLY appreciate it!!

Shawn.


--
smpayne69
------------------------------------------------------------------------
smpayne69's Profile:
http://www.excelforum.com/member.php...o&userid=29307
View this thread: http://www.excelforum.com/showthread...hreadid=490251

Covert UTC date
 

Thank you for your assistance!

Excuse me for my ignorance with Access and VBA, but how can I apply
this in Access? I have a table that has times like 3098739942 and want
to have it converted during the query so that the new table has correct
time values.

Do I make a small module with the command you gave me and call it
during the query, or something??

Thanks again!!


--
smpayne69
------------------------------------------------------------------------
smpayne69's Profile: http://www.excelforum.com/member.php...o&userid=29307
View this thread: http://www.excelforum.com/showthread...hreadid=490251