Convert GPS Date and Time

[Scott]

I have a date/time field from a GPS device that I need to convert to a date
and time field in Access.

The data in the "STARTTIME" field is "2008-05-07T10:38:00Z". I want to
display it in a query in two fields, a date field and a time field. How do I
convert "2008-05-07T10:38:00Z" to display as "05/07/2008" in a date field and
"10:38 AM" in a time field?

Thank you,
Scott

 

[Jerry Whittle]

Assuming that the string is always the same number of characters; has a T in
it; and represents valid dates and times, the following should work. The date
and time format won't be exactly as you ask; however, they will be valid
dates and time.

TheDate: CDate(Left([STARTTIME], Instr([STARTTIME],"T")-1))

TheTime: CDate(Mid([STARTTIME], Instr([STARTTIME],"T")+1,8))

 

[Scott]

Perfect! Thank you. Could I ask for your help in taking it one step
further? The time is showing GMT which I need to convert to MST or back 6
hours and can the time be shown in "h:nn" or "h:nn:00" instead of "h:nn:ss".
So, "10:38:51 AM" will show in MST as "4:38 AM" or "4:38:00 AM"?

Thanks,
Scott

Assuming that the string is always the same number of characters; has a T in
it; and represents valid dates and times, the following should work. The date
and time format won't be exactly as you ask; however, they will be valid
dates and time.

TheDate: CDate(Left([STARTTIME], Instr([STARTTIME],"T")-1))

TheTime: CDate(Mid([STARTTIME], Instr([STARTTIME],"T")+1,8))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a date/time field from a GPS device that I need to convert to a date
and time field in Access.

The data in the "STARTTIME" field is "2008-05-07T10:38:00Z". I want to
display it in a query in two fields, a date field and a time field. How do I
convert "2008-05-07T10:38:00Z" to display as "05/07/2008" in a date field and
"10:38 AM" in a time field?

Thank you,
Scott