Post by R.WieserHello All,
I'm using XMLDOM to extract some data from an RSS feed, and would now like
to convert the returned 'pubDate", "dc:date" and others like it dates into a
Mon, 31 Oct 2016 00:00:00 -0600
2016-10-28T07:00:00+00:00
The build-in "CDate" method can't handle the above.
I could just throw some homebrewn parsing-fu at it, but if there is alrady a
build-in method I would rather use that. :-)
Does anybody know if there is a build-in method to convert the above two
examples into a VBS date format ?
Regards,
Rudy Wieser
First parse off the trailing timezone adjustment, after CDate() do a dateAdd()
with it but note that it may be in minutes or hhmm depending on the format.
For the 1st format RFC822 remove redundant "dayname, " and CDate() it.
For the 2nd format ISO8601 ,replace the T with a space and CDate() will parse it.
Atom feeds use format RFC3339 which is similar to ISO8601 but demands an
Upcase T separator (8601 can be space or t or T) & uses Z for zulu tz (GMT).
Function RFC822ToVB( ByVal aRFC822Date )
' pass in an RFC822 date string, return a vb date.
'
' to convert a proper RFC822 date into a vbDate, delete the "dayname, " prefix
' (chars 1..5) & timezone parts (26..32), put a space between the date & time
' (char 17) and call CDate(). DateAdd() to adjust for timezone if needed.
' 12345678901234567890123456789012
' Mon, 29 Jan 2018 06:31:23 -0500
Dim tz, hrs, mns
aRFC822Date = Trim(aRFC822Date)
' save the timezone its +|-nnnn or char(1..3) name
tz = Trim(Remove(Mid(aRFC822Date,26),":",""))
aRFC822Date = Mid(Mid(Trim(aRFC822Date),1,25),6)
If Not IsDate(aRFC822Date) Then aRFC822Date = Mid(aRFC822Date,16) & " " & Mid(aRFC822Date,18) ' remove T before time.
If Not IsDate(aRFC822Date) Then Exit Function ' beats me man.
RFC822ToVB = CDate(aRFC822Date)
If (5 = Len(tz)) And IsNumeric(tz) Then ' handle +|-hhmm
hrs = CInt(Left(tz, 3))
mns = Sgn(tz) * CInt(Right(tz, 2))
RFC822ToVB = DateAdd( "n" , mns , DateAdd( "H" , hrs , CDate(aRFC822Date) ))
Else
' dict lookup the TZ codename => hours +/- of GMT or Z/Zulu.
If dctTZNamesISO.Exists(LCase(tz)) Then
hrs = CInt( dctTZNamesISO.Item(LCase(tz)) )
mns = CInt(00)
RFC822ToVB = DateAdd( "n" , mns , DateAdd( "H" , hrs , CDate(aRFC822Date) ))
End If
End If
End Function
Going the other way, If you can instance a Dot.Net StringBuilder object,
its AppendFormat() call can easily make an ISO format date string for
a given vb date.
Function FormatDateRFC822( aDate )
' RFC822 Datetime convert a vb date into 'day, dd mon yyyy hh:nn:ss GMT' RFC822 string.
' 12345678901234567890123456789012
' Mon, 29 Jan 2018 06:31:23 -0500
' dot net formatter handles local time zone for us but its
' always +|-hh:mm +|-hhmm also correct for RFC822 format
' so use Remove(CharPos,CharLen) to remove ':' at col 30.
With CreateObject("System.Text.StringBuilder")
.Length = 0 ' clear the builder
.AppendFormat("{0:ddd, dd MMM yyyy HH:mm:ss zzzz}" , aDate ).Remove(30,1)
FormatDateRFC822 = .ToString()
End With
End Function
Function FormatDateISO8601( aDate )
' ISO8601DateTime via dotnet stringbuilder
If Not IsDate(aDate) Then Exit Function
With CreateObject("System.Text.StringBuilder")
.Length = 0 ' clear the builder
' ISO8601DateTime YYYY-MM-DDThh:mm:ss-05:00
.AppendFormat("{0:yyyy-MM-ddTHH:mm:sszzzzzz}" , aDate)
FormatDateISO8601 = .ToString()
End With
End Function
Function FormatDateRFC3339( aDate )
' RFC3339 Upcase T date/time sep and Z for zulu tz or +|-hh:mm
' Complete date plus hours and minutes:
' YYYY-MM-DDThh:mmTZD (eg 1997-07-16T19:20+01:00)
' Complete date plus hours, minutes and seconds:
' YYYY-MM-DDThh:mm:ssTZD (eg 1997-07-16T19:20:30+01:00)
' Complete date plus hours, minutes, seconds and a decimal fraction of a second
' YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)
With CreateObject("System.Text.StringBuilder")
.Length = 0 ' clear the builder
' RFC3339 YYYY-MM-DDThh:mm:ssTZD (eg 1997-07-16T19:20:30+01:00)
.AppendFormat("{0:yyyy-MM-ddTHH:mm:sszzzzzz}" , aDate)
FormatDateRFC3339 = .ToString()
End With
End Function