Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1001
  • Last Modified:

Convert GMT Date/Time with Time Zone

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
KANEDA 0149
Asked:
KANEDA 0149
  • 2
  • 2
  • 2
  • +1
1 Solution
 
COACHMAN99Commented:
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
 
Gustav BrockCIOCommented:
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
 
COACHMAN99Commented:
I forgot the time diff (dateadd) :-)
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
KANEDA 0149Author Commented:
Thank you Gustav, that worked exactly as I needed.
0
 
KANEDA 0149Author Commented:
Thank you too COACHMAN99, your original returned 6:43 versus 2:43. Which is the time diff missing.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The Master of Date/Time to the rescue ... !
:-)
0
 
Gustav BrockCIOCommented:
Thanks!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now