Convert Access DateTime into KLM for use in Google timeline

[andrewpa...@hotmail.com]

Can someone give me the right code to convert ms Access date/time info
into a KLM stream.
This attempt throws "format" errors at the format(RS point.
("timestamp" is the Access database fieldname) in the format dd/mm/
yyyy hh:mm:ss

Response.Write "<Placemark>"
Response.Write "<TimeStamp><when>"& format(RS("timestamp"), "yyyy-mm-
ddThh:mm:ssZ") &"</when></TimeStamp>"



NB I also have a Data.xml cache question, which no one has a view
yet.
http://groups.google.com/group/kml-support-getting-started/browse_thread/thread/6dd7c3efb0e1e727

Any help appreciated,
Regards Paul

[andrewpa...@hotmail.com]

I had a thought ~ format it in the database! Used this format string
yyyy-mm-dd"T "hh:nn:ss\Z and lo, the database gives the right output
yyyy-mm-ddThh:nn:ssZ however when I download the file Google Earth
says "Could not parse time value 7/15/2008 6:45:37 AM so the new code
I tried

Response.Write "<TimeStamp><when>"& RS("timestamp") &"</when></
TimeStamp>"

Obviously sucks UNFORMATTED output from MS Access. Boo! I still need
to format it to GE requirements yyyy-mm-ddThh:nn:ssZ

[nicodemussunrise]

Andrew,

Could you upload some of your KML (the output from whatever script you
have generating it). I think that will help me diagnose.

-ns

[ManoM]

Hi,

Do a search on MS Access and UTC time, you'll probably find what you
need.

Mano

[andrewpa...@hotmail.com]

nicodemussunrise,

Problem code is in viewroute.asp (which saves SocialTracking.kml to
your local machine).
If I remove the line Response.Write "<TimeStamp><when>"&

RS("timestamp") &"</when></TimeStamp>"

it works fine (obviously with no date info).

www.paulscorner.info/index.asp for the page to enter data,
www.paulscorner.info/tracking.asp loads the info to the database
and writes the server data.xml file
www.paulscorner.info/result.html for the results page (with
test data for now) and a link to
www.paulscorner.info/viewroute.asp for the file I am trying to
debug

Regards Paul

[andrewpa...@hotmail.com]

Thanks ManoM

> Do a search on MS Access and UTC time, you'll probably find what you need.

Unfortunately that not the issue. The output format Google needs is
yyyy-mm-dd"T "hh:nn:ss\Z not UTC.
I can format inside access but when I use Response.Write
RS("timestamp") the format is dd-mm-yyyy hh:mm:ss (missing the T, the
Z and in the wrong order!).
I'm assuming the data is stored in the database as above and I have to
convert it serverside, but I don't know how.

Regards Paul

[andrewpa...@hotmail.com]

OK, probably a bit clunky, but I got this working and have posted the
result here a) to help a similar problem and b) in case someone has a
smarter solution.

Add this function to the page

Function fDateFormat(DateIn)

If IsNull(DateIn) Or DateIn = "" Then
fDateFormat = ""
Else

Dim intMonth
Dim strMonth
Dim intDay
Dim strDay
Dim intHour
Dim strHour
Dim intMinute
Dim strMinute
Dim intSecond
Dim strSecond

intDay = DatePart("d",DateIn)
If Len(intDay) = 1 Then
strDay = "0" & intDay
Else
strDay = intDay
End If

intMonth = DatePart("m",DateIn)
If Len(intMonth) = 1 Then
strMonth = "0" & intMonth
Else
strMonth = intMonth
End If

intHour = DatePart("h",DateIn)
If Len(intHour) = 1 Then
strHour = "0" & intHour
Else
strHour = intHour
End If

intMinute = DatePart("n",DateIn)
If Len(intMinute) = 1 Then
strMinute = "0" & intMinute
Else
strMinute = intMinute
End If

intSecond = DatePart("s",DateIn)
If Len(intSecond) = 1 Then
strSecond = "0" & intSecond
Else
strSecond = intSecond
End If

fDateFormat = DatePart("yyyy",DateIn) & "-" & strMonth & "-" & strDay
& "T" & strHour & ":" & strMinute & ":" & strSecond & "Z"

End If

End Function

and call it like this

Response.Write "<TimeStamp><when>"& fDateFormat(RS("timestamp")) &"</
when></TimeStamp>"