 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008convert GMT Date & Time
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 NewDate: DateValue(DateAdd("h",-6,[StartTime])) NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time")  SignatureKARL 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 [quoted text clipped - 23 lines] > Thank you, > 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 > NewDate: DateValue(DateAdd("h",-6,[StartTime])) > NewTime: Format(DateAdd("h",-6,[StartTime]), "Medium Time") [quoted text clipped - 26 lines] > > Thank you, > > Scott 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") SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (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 [quoted text clipped - 41 lines] >> > Thank you, >> > 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 > I already told you how to convert that string into a proper date/time two > days ago, Scott: [quoted text clipped - 53 lines] > >> > Thank you, > >> > Scott 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. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > You are awesome! Thank you. You're right, you gave me the solution for > the [quoted text clipped - 74 lines] >> >> > Thank you, >> >> > Scott That's what I was doing. Thanks again for your help! > Were you trying to use DateAdd on just the time? > [quoted text clipped - 86 lines] > >> >> > Thank you, > >> >> > Scott |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.