Solved

Convert GMT Date/Time with Time Zone

Posted on 2014-04-11
7
967 Views
Last Modified: 2014-04-11
In access query, how do you convert a date/time with time zone in GMT format into standard Eastern GMT.  For example:

ORIGINAL FIELD VALUE
2013-07-25T18:43:01.000Z

UPDATED FIELD VALUE
7/25/2013 2:43 PM


Thanks!
0
Comment
Question by:KANEDA 0149
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39994696
try:
CDate(Replace(Left("2013-07-25T18:43:01.000Z", InStr(1, "2013-07-25T18:43:01.000Z", ".") - 1), "T", " "))

not pretty but works :-)
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39994708
First convert to date/time:

strTime = "2013-07-25T18:43:01.000Z"

datTime = CDate(Left(Replace(strTime, "T", " "), 19))

Then expand to convert to local time:

datTime = DateAdd("h", -4, CDate(Left(Replace(strTime, "T", " "), 19)))

/gustav
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39994750
I forgot the time diff (dateadd) :-)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:KANEDA 0149
ID: 39994757
Thank you Gustav, that worked exactly as I needed.
0
 

Author Comment

by:KANEDA 0149
ID: 39994762
Thank you too COACHMAN99, your original returned 6:43 versus 2:43. Which is the time diff missing.
0
 
LVL 75
ID: 39994802
The Master of Date/Time to the rescue ... !
:-)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39994940
Thanks!

/gustav
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question