convert GMT Date & Time

S

Scott

I need the STARTTIME below formatted to display two separate fields in a
query. Field1 will be NewDate and Field2 will be NewTime. The STARTTIME is
from a GPS device in GMT. I need the new date and time to display in current
time (GMT minus six hours or MST). Also, when the GMT is 00:00:00 through
06:00:00 the date needs to change because when 2008-05-14 at 02:00 AM is
adjusted to MST the date and time become 2008-05-13 at 8:00 PM. I’ve noted
the times where the date needs to change.

STARTTIME NewDate NewTime Notes
2008-05-21T10:17:49Z Convert to 5/21/2008 4:17 AM
2008-05-17T13:09:13Z Convert to 5/17/2008 7:09 AM
2008-05-14T02:00:44Z Convert to 5/13/2008 8:00 PM Notice Date change
2008-05-10T15:05:42Z Convert to 5/10/2008 9:05 AM
2008-05-07T10:38:00Z Convert to 5/7/2008 4:38 AM
2008-05-05T10:57:19Z Convert to 5/5/2008 4:57 AM
2008-05-03T12:49:22Z Convert to 5/3/2008 6:49 AM
2008-05-01T10:58:57Z Convert to 5/1/2008 4:58 AM
2008-05-01T01:52:07Z Convert to 4/30/2008 7:52 PM Notice Date change
2008-04-29T01:25:58Z Convert to 4/28/2008 7:25 PM Notice Date change
2008-04-26T11:49:02Z Convert to 4/26/2008 5:49 AM
2008-04-25T23:51:03Z Convert to 4/25/2008 5:51 PM
2008-04-19T11:36:45Z Convert to 4/19/2008 5:36 AM
2008-04-17T23:41:01Z Convert to 4/17/2008 5:41 PM
2008-04-16T02:05:20Z Convert to 4/15/2008 8:05 PM Notice Date change

Thank you,
Scott
 
K

KARL DEWEY

NewDate: DateValue(DateAdd("h",-6,[StartTime]))
NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")
 
S

Scott

Did you create the data fields and get test the solution? The solution
returned an error in my database. I think it is because the STARTTIME
date/time field has a "T" and "Z" value in the data so it has to be converted
first.

Thanks,
Scott

KARL DEWEY said:
NewDate: DateValue(DateAdd("h",-6,[StartTime]))
NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")

--
KARL DEWEY
Build a little - Test a little


Scott said:
I need the STARTTIME below formatted to display two separate fields in a
query. Field1 will be NewDate and Field2 will be NewTime. The STARTTIME is
from a GPS device in GMT. I need the new date and time to display in current
time (GMT minus six hours or MST). Also, when the GMT is 00:00:00 through
06:00:00 the date needs to change because when 2008-05-14 at 02:00 AM is
adjusted to MST the date and time become 2008-05-13 at 8:00 PM. I’ve noted
the times where the date needs to change.

STARTTIME NewDate NewTime Notes
2008-05-21T10:17:49Z Convert to 5/21/2008 4:17 AM
2008-05-17T13:09:13Z Convert to 5/17/2008 7:09 AM
2008-05-14T02:00:44Z Convert to 5/13/2008 8:00 PM Notice Date change
2008-05-10T15:05:42Z Convert to 5/10/2008 9:05 AM
2008-05-07T10:38:00Z Convert to 5/7/2008 4:38 AM
2008-05-05T10:57:19Z Convert to 5/5/2008 4:57 AM
2008-05-03T12:49:22Z Convert to 5/3/2008 6:49 AM
2008-05-01T10:58:57Z Convert to 5/1/2008 4:58 AM
2008-05-01T01:52:07Z Convert to 4/30/2008 7:52 PM Notice Date change
2008-04-29T01:25:58Z Convert to 4/28/2008 7:25 PM Notice Date change
2008-04-26T11:49:02Z Convert to 4/26/2008 5:49 AM
2008-04-25T23:51:03Z Convert to 4/25/2008 5:51 PM
2008-04-19T11:36:45Z Convert to 4/19/2008 5:36 AM
2008-04-17T23:41:01Z Convert to 4/17/2008 5:41 PM
2008-04-16T02:05:20Z Convert to 4/15/2008 8:05 PM Notice Date change

Thank you,
Scott
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I already told you how to convert that string into a proper date/time two
days ago, Scott:

CDate(Replace(Replace([id], "T", " "), "Z", "")))

That means use

NewDate: DateValue(DateAdd("h",-6,CDate(Replace(Replace([StartTime], "T",
" "), "Z", "")))))
NewTime: Format(DateAdd("h",-6,CDate(Replace(Replace([StartTime], "T", "
"), "Z", "")))), "Medium Time")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Scott said:
Did you create the data fields and get test the solution? The solution
returned an error in my database. I think it is because the STARTTIME
date/time field has a "T" and "Z" value in the data so it has to be
converted
first.

Thanks,
Scott

KARL DEWEY said:
NewDate: DateValue(DateAdd("h",-6,[StartTime]))
NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")

--
KARL DEWEY
Build a little - Test a little


Scott said:
I need the STARTTIME below formatted to display two separate fields in
a
query. Field1 will be NewDate and Field2 will be NewTime. The
STARTTIME is
from a GPS device in GMT. I need the new date and time to display in
current
time (GMT minus six hours or MST). Also, when the GMT is 00:00:00
through
06:00:00 the date needs to change because when 2008-05-14 at 02:00 AM
is
adjusted to MST the date and time become 2008-05-13 at 8:00 PM. I've
noted
the times where the date needs to change.

STARTTIME NewDate NewTime Notes
2008-05-21T10:17:49Z Convert to 5/21/2008 4:17 AM
2008-05-17T13:09:13Z Convert to 5/17/2008 7:09 AM
2008-05-14T02:00:44Z Convert to 5/13/2008 8:00 PM Notice Date change
2008-05-10T15:05:42Z Convert to 5/10/2008 9:05 AM
2008-05-07T10:38:00Z Convert to 5/7/2008 4:38 AM
2008-05-05T10:57:19Z Convert to 5/5/2008 4:57 AM
2008-05-03T12:49:22Z Convert to 5/3/2008 6:49 AM
2008-05-01T10:58:57Z Convert to 5/1/2008 4:58 AM
2008-05-01T01:52:07Z Convert to 4/30/2008 7:52 PM Notice Date change
2008-04-29T01:25:58Z Convert to 4/28/2008 7:25 PM Notice Date change
2008-04-26T11:49:02Z Convert to 4/26/2008 5:49 AM
2008-04-25T23:51:03Z Convert to 4/25/2008 5:51 PM
2008-04-19T11:36:45Z Convert to 4/19/2008 5:36 AM
2008-04-17T23:41:01Z Convert to 4/17/2008 5:41 PM
2008-04-16T02:05:20Z Convert to 4/15/2008 8:05 PM Notice Date change

Thank you,
Scott
Click to expand...
Click to expand...
Click to expand...
 
S

Scott

You are awesome! Thank you. You're right, you gave me the solution for the
conversion two days ago but I still needed to back up the GMT to local time
and when I tried to do that using the DateAdd function I would get data like
"12/29/1899 8:00:44 PM" (the year 1899?) if it crossed back into the previous
day. Obviously, I wasn't doing something right.

Thanks for your help and yes you did answer both my questions correctly; I'm
set!
Scott


Douglas J. Steele said:
I already told you how to convert that string into a proper date/time two
days ago, Scott:

CDate(Replace(Replace([id], "T", " "), "Z", "")))

That means use

NewDate: DateValue(DateAdd("h",-6,CDate(Replace(Replace([StartTime], "T",
" "), "Z", "")))))
NewTime: Format(DateAdd("h",-6,CDate(Replace(Replace([StartTime], "T", "
"), "Z", "")))), "Medium Time")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Scott said:
Did you create the data fields and get test the solution? The solution
returned an error in my database. I think it is because the STARTTIME
date/time field has a "T" and "Z" value in the data so it has to be
converted
first.

Thanks,
Scott

KARL DEWEY said:
NewDate: DateValue(DateAdd("h",-6,[StartTime]))
NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")

--
KARL DEWEY
Build a little - Test a little


:

I need the STARTTIME below formatted to display two separate fields in
a
query. Field1 will be NewDate and Field2 will be NewTime. The
STARTTIME is
from a GPS device in GMT. I need the new date and time to display in
current
time (GMT minus six hours or MST). Also, when the GMT is 00:00:00
through
06:00:00 the date needs to change because when 2008-05-14 at 02:00 AM
is
adjusted to MST the date and time become 2008-05-13 at 8:00 PM. I've
noted
the times where the date needs to change.

STARTTIME NewDate NewTime Notes
2008-05-21T10:17:49Z Convert to 5/21/2008 4:17 AM
2008-05-17T13:09:13Z Convert to 5/17/2008 7:09 AM
2008-05-14T02:00:44Z Convert to 5/13/2008 8:00 PM Notice Date change
2008-05-10T15:05:42Z Convert to 5/10/2008 9:05 AM
2008-05-07T10:38:00Z Convert to 5/7/2008 4:38 AM
2008-05-05T10:57:19Z Convert to 5/5/2008 4:57 AM
2008-05-03T12:49:22Z Convert to 5/3/2008 6:49 AM
2008-05-01T10:58:57Z Convert to 5/1/2008 4:58 AM
2008-05-01T01:52:07Z Convert to 4/30/2008 7:52 PM Notice Date change
2008-04-29T01:25:58Z Convert to 4/28/2008 7:25 PM Notice Date change
2008-04-26T11:49:02Z Convert to 4/26/2008 5:49 AM
2008-04-25T23:51:03Z Convert to 4/25/2008 5:51 PM
2008-04-19T11:36:45Z Convert to 4/19/2008 5:36 AM
2008-04-17T23:41:01Z Convert to 4/17/2008 5:41 PM
2008-04-16T02:05:20Z Convert to 4/15/2008 8:05 PM Notice Date change

Thank you,
Scott
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Were you trying to use DateAdd on just the time?

Access really doesn't support time-only. The Date/Time data type is an eight
byte floating point number where the integer portion represents the date as
the number of days relative to 30 Dec, 1899, and the decimal portion
represents the time as a fraction of a day. When you store only a time,
Access leave the integer portion as 0, which means that particular time on
30 Dec, 1899. Doing arithmetic on the time, therefore, can lead to a
different time on a different day. That's also the reason why you cannot add
times together in Access: once the total exceeds 24 hours (1.0), you get a
time on a different day.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Scott said:
You are awesome! Thank you. You're right, you gave me the solution for
the
conversion two days ago but I still needed to back up the GMT to local
time
and when I tried to do that using the DateAdd function I would get data
like
"12/29/1899 8:00:44 PM" (the year 1899?) if it crossed back into the
previous
day. Obviously, I wasn't doing something right.

Thanks for your help and yes you did answer both my questions correctly;
I'm
set!
Scott


Douglas J. Steele said:
I already told you how to convert that string into a proper date/time two
days ago, Scott:

CDate(Replace(Replace([id], "T", " "), "Z", "")))

That means use

NewDate: DateValue(DateAdd("h",-6,CDate(Replace(Replace([StartTime],
"T",
" "), "Z", "")))))
NewTime: Format(DateAdd("h",-6,CDate(Replace(Replace([StartTime], "T",
"
"), "Z", "")))), "Medium Time")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Scott said:
Did you create the data fields and get test the solution? The solution
returned an error in my database. I think it is because the STARTTIME
date/time field has a "T" and "Z" value in the data so it has to be
converted
first.

Thanks,
Scott

:

NewDate: DateValue(DateAdd("h",-6,[StartTime]))
NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")

--
KARL DEWEY
Build a little - Test a little


:

I need the STARTTIME below formatted to display two separate fields
in
a
query. Field1 will be NewDate and Field2 will be NewTime. The
STARTTIME is
from a GPS device in GMT. I need the new date and time to display
in
current
time (GMT minus six hours or MST). Also, when the GMT is 00:00:00
through
06:00:00 the date needs to change because when 2008-05-14 at 02:00
AM
is
adjusted to MST the date and time become 2008-05-13 at 8:00 PM.
I've
noted
the times where the date needs to change.

STARTTIME NewDate NewTime Notes
2008-05-21T10:17:49Z Convert to 5/21/2008 4:17 AM
2008-05-17T13:09:13Z Convert to 5/17/2008 7:09 AM
2008-05-14T02:00:44Z Convert to 5/13/2008 8:00 PM Notice Date change
2008-05-10T15:05:42Z Convert to 5/10/2008 9:05 AM
2008-05-07T10:38:00Z Convert to 5/7/2008 4:38 AM
2008-05-05T10:57:19Z Convert to 5/5/2008 4:57 AM
2008-05-03T12:49:22Z Convert to 5/3/2008 6:49 AM
2008-05-01T10:58:57Z Convert to 5/1/2008 4:58 AM
2008-05-01T01:52:07Z Convert to 4/30/2008 7:52 PM Notice Date change
2008-04-29T01:25:58Z Convert to 4/28/2008 7:25 PM Notice Date change
2008-04-26T11:49:02Z Convert to 4/26/2008 5:49 AM
2008-04-25T23:51:03Z Convert to 4/25/2008 5:51 PM
2008-04-19T11:36:45Z Convert to 4/19/2008 5:36 AM
2008-04-17T23:41:01Z Convert to 4/17/2008 5:41 PM
2008-04-16T02:05:20Z Convert to 4/15/2008 8:05 PM Notice Date change

Thank you,
Scott
Click to expand...
Click to expand...
Click to expand...
 
S

Scott

That's what I was doing. Thanks again for your help!

Douglas J. Steele said:
Were you trying to use DateAdd on just the time?

Access really doesn't support time-only. The Date/Time data type is an eight
byte floating point number where the integer portion represents the date as
the number of days relative to 30 Dec, 1899, and the decimal portion
represents the time as a fraction of a day. When you store only a time,
Access leave the integer portion as 0, which means that particular time on
30 Dec, 1899. Doing arithmetic on the time, therefore, can lead to a
different time on a different day. That's also the reason why you cannot add
times together in Access: once the total exceeds 24 hours (1.0), you get a
time on a different day.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Scott said:
You are awesome! Thank you. You're right, you gave me the solution for
the
conversion two days ago but I still needed to back up the GMT to local
time
and when I tried to do that using the DateAdd function I would get data
like
"12/29/1899 8:00:44 PM" (the year 1899?) if it crossed back into the
previous
day. Obviously, I wasn't doing something right.

Thanks for your help and yes you did answer both my questions correctly;
I'm
set!
Scott


Douglas J. Steele said:
I already told you how to convert that string into a proper date/time two
days ago, Scott:

CDate(Replace(Replace([id], "T", " "), "Z", "")))

That means use

NewDate: DateValue(DateAdd("h",-6,CDate(Replace(Replace([StartTime],
"T",
" "), "Z", "")))))
NewTime: Format(DateAdd("h",-6,CDate(Replace(Replace([StartTime], "T",
"
"), "Z", "")))), "Medium Time")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Did you create the data fields and get test the solution? The solution
returned an error in my database. I think it is because the STARTTIME
date/time field has a "T" and "Z" value in the data so it has to be
converted
first.

Thanks,
Scott

:

NewDate: DateValue(DateAdd("h",-6,[StartTime]))
NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")

--
KARL DEWEY
Build a little - Test a little


:

I need the STARTTIME below formatted to display two separate fields
in
a
query. Field1 will be NewDate and Field2 will be NewTime. The
STARTTIME is
from a GPS device in GMT. I need the new date and time to display
in
current
time (GMT minus six hours or MST). Also, when the GMT is 00:00:00
through
06:00:00 the date needs to change because when 2008-05-14 at 02:00
AM
is
adjusted to MST the date and time become 2008-05-13 at 8:00 PM.
I've
noted
the times where the date needs to change.

STARTTIME NewDate NewTime Notes
2008-05-21T10:17:49Z Convert to 5/21/2008 4:17 AM
2008-05-17T13:09:13Z Convert to 5/17/2008 7:09 AM
2008-05-14T02:00:44Z Convert to 5/13/2008 8:00 PM Notice Date change
2008-05-10T15:05:42Z Convert to 5/10/2008 9:05 AM
2008-05-07T10:38:00Z Convert to 5/7/2008 4:38 AM
2008-05-05T10:57:19Z Convert to 5/5/2008 4:57 AM
2008-05-03T12:49:22Z Convert to 5/3/2008 6:49 AM
2008-05-01T10:58:57Z Convert to 5/1/2008 4:58 AM
2008-05-01T01:52:07Z Convert to 4/30/2008 7:52 PM Notice Date change
2008-04-29T01:25:58Z Convert to 4/28/2008 7:25 PM Notice Date change
2008-04-26T11:49:02Z Convert to 4/26/2008 5:49 AM
2008-04-25T23:51:03Z Convert to 4/25/2008 5:51 PM
2008-04-19T11:36:45Z Convert to 4/19/2008 5:36 AM
2008-04-17T23:41:01Z Convert to 4/17/2008 5:41 PM
2008-04-16T02:05:20Z Convert to 4/15/2008 8:05 PM Notice Date change

Thank you,
Scott
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert Date and Time 9
Time Left 4
selected Calendar date function to test for Holiday 1
Convert Date/Time in VBA 2
Compare values from one month to next 2
Date / Time problem 4
select entries keyed in after 5:30 PM 1
Sumproduct 1

Top