hello!
please add a couple of functions to work with JSON date as JScript Date (milliseconds) vba-> ISO 8601 (milliseconds) with GMT offset
to exchange the VBA-JSON date format like the style {"IssuedFrom": "\ / Date (928135200000 + 0300) \ /"}
for example (my functions ...)
Public Const utcOffset = 3 'GMT offset for Moscow +3 hours to UTC
'---------------------------------------------------------------------------------------------------------------
'convert local time to UTC format taking into account the specified GMT offset
'(for working with requests to the server in JSON format)
'---------------------------------------------------------------------------------------------------------------
' input_datetime - local date time as a string
' utc_offset - time zone offset (number in hours)
Function ConvertToUnixTimeStamp(input_datetime, utc_offset) 'As String
Dim d
d = CDate(input_datetime)
d = DateAdd("h", 0 - utc_offset, d) ' GMT offset Moskow (+3h)
ConvertToUnixTimeStamp = CStr(DateDiff("s", "01/01/1970 00:00:00", d) * 1000)
End Function
my script
vbscript: ConvertToUnixTimeStamp("2021-09-30 00:00:00", utcOffset) = 1632949200000 'correct with GMT Moskow +0300
JScritp: - new Date("2021-09-30 00:00:00").getTime()=1632949200000 'correct
you script
to conversion
you vbscript: JsonConverter.ConvertToIso("2021-09-30 00:00:00") = "2021-09-29T21:00:00.000Z"
JScript: - new Date("2021-09-29T21:00:00.000Z").getTime() = 1632949200000 'correct
but the web service (with which I work) incorrectly handles such a record)
"""IssuedFrom"":""" & "\/Date(" & CStr(ConvertToIso("2021-09-30 00:00:00")) & ")\/" & """, "
or
"""IssuedFrom"":""" & "\/Date(" & CStr(ConvertToIso("2021-09-30 00:00:00")) & ").getTime()\/" & """
it's possible that I'm not passing new (I don't know how it's handled on the other side)
this is how it works - but the recording is not very beautiful
"""IssuedFrom"":""" & "\/Date(" & CStr(ConvertToUnixTimeStamp("2021-09-30 00:00:00", utcOffset)) & ")\/" & """
but it would be better like this
"""IssuedFrom"":""" & "\/Date(1632949200000 )\/" & """, "
can you do this?
... JSONIsoMsToDateVBA - input
in JSON = "/ Date (-2209172400000 + 0300) /" or "/ Date (1533416400000 + 0300) /" etc.
'---------------------------------------------------------------------------------------------------------------
'convert UTC format to local time (for working with server responses in JSON format)
'---------------------------------------------------------------------------------------------------------------
'input_datetime - date time in UTC format (string, here in milliseconds) including the GMT offset
Function ConvertUnixTimeStampToDateTime(input_unix_timestamp) 'As String [regular datetime]
ConvertUnixTimeStampToDateTime = CStr(DateAdd("s", input_unix_timestamp, "01/01/1970 00:00:00"))
End Function
'---------------------------------------------------------------------------------------------------------------
'parsing the date received from the server in JSON UTC format containing the offset
'---------------------------------------------------------------------------------------------------------------
'inDate - string for decoding type ("/ Date (1533416400000 + 0300) /", "/ Date (-2209172400000 + 0300) /, " etc.)
'**for strings ("/ Date (1632715104000) /" (offset already in data) - use a constant utcOffset +3h Moskow
' utc_offset - time zone offset (number in hours)
Function parseJSONdate(inDate, utc_offset)
Dim offsetFromString, tmp, d, utc
If InStr(inDate, "(") > 0 And InStr(inDate, ")") > 0 Then
tmp = Trim(Mid(inDate, InStr(inDate, "(") + 1, InStr(inDate, ")") - InStr(inDate, "(") - 1))
Else
tmp = inDate
End If
If Left(tmp, 1) = "-" Then
parseJSONdate = Format(CDate("01/01/1970 00:00:00"), "yyyy-mm-dd hh:mm:ss")
Else
If InStr(tmp, "+") > 0 Then
utc = CDbl(Left(tmp, InStr(tmp, "+") - 1))
offsetFromString = CDbl(Right(tmp, Len(tmp) - InStr(tmp, "+"))) / 100
ElseIf InStr(tmp, "-") > 0 Then
utc = CDbl(Left(tmp, InStr(tmp, "-") - 1))
offsetFromString = 0 - CDbl(Right(tmp, Len(tmp) - InStr(tmp, "-"))) / 100
Else
utc = CDbl(tmp)
offsetFromString = utc_offset '!!! perhaps the date will already contain GMT offset in an implicit form
End If
d = CDate(ConvertUnixTimeStampToDateTime(CStr(utc / 1000)))
d = DateAdd("h", 0 + offsetFromString, d) ' GMT offset Moskow (+3h)
parseJSONdate = Format(d, "yyyy-mm-dd hh:mm:ss")
End If
End Function
test my script
date <"01/01/1970 00:00:00" - i skip
vbscript ms = parseJSONdate ("/ Date (-2209172400000 + 0300) /", utcOffset )
' "01/01/1970 00:00:00" (original - Fri Dec 29 1899 23:30:17 GMT + 0230 (Moscow, standard time )
date >"01/01/1970 00:00:00"
vbscript - parseJSONdate ("/ Date (1533416400000 + 0300) /", utcOffset) = "2018-08-05 00:00:00" 'correct
JScript
var f = new Date(1533416400000 + 0300) = Sun Aug 05 2018 00:00:00 GMT+0300 'correct
document.write (f.getMonth()+1+"/"+f.getDate()+"/"+f.getFullYear() +" " + f.getHours()+":"+f.getMinutes()+":"+f.getSeconds() +"\n ") = 8/5/2018 0:0:0 'correct
in general, it works of course, but the code is not beautiful, you have more experience - maybe you can offer your own version for your library, taking into account GMT
hello! please add a couple of functions to work with JSON date as JScript Date (milliseconds) vba-> ISO 8601 (milliseconds) with GMT offset to exchange the VBA-JSON date format like the style {"IssuedFrom": "\ / Date (928135200000 + 0300) \ /"}
for example (my functions ...)
Public Const utcOffset = 3 'GMT offset for Moscow +3 hours to UTC
... DateVBAToJSONIsoMs - output
my script vbscript: ConvertToUnixTimeStamp("2021-09-30 00:00:00", utcOffset) = 1632949200000 'correct with GMT Moskow +0300 JScritp: - new Date("2021-09-30 00:00:00").getTime()=1632949200000 'correct
you script to conversion you vbscript: JsonConverter.ConvertToIso("2021-09-30 00:00:00") = "2021-09-29T21:00:00.000Z"
JScript: - new Date("2021-09-29T21:00:00.000Z").getTime() = 1632949200000 'correct
reverse conversion f = new Date(1632949200000); document.write (f+ "\n ") = Thu Sep 30 2021 00:00:00 GMT+0300 (Москва, стандартное время) document.write (f.getMonth()+1 +"/"+f.getDate()+"/"+f.getFullYear() +" " + f.getHours()+":"+f.getMinutes()+":"+f.getSeconds() +"\n ") = 9/30/2021 0:0:0 'correct
but the web service (with which I work) incorrectly handles such a record) """IssuedFrom"":""" & "\/Date(" & CStr(ConvertToIso("2021-09-30 00:00:00")) & ")\/" & """, " or """IssuedFrom"":""" & "\/Date(" & CStr(ConvertToIso("2021-09-30 00:00:00")) & ").getTime()\/" & """ it's possible that I'm not passing new (I don't know how it's handled on the other side)
this is how it works - but the recording is not very beautiful """IssuedFrom"":""" & "\/Date(" & CStr(ConvertToUnixTimeStamp("2021-09-30 00:00:00", utcOffset)) & ")\/" & """ but it would be better like this """IssuedFrom"":""" & "\/Date(1632949200000 )\/" & """, " can you do this?
... JSONIsoMsToDateVBA - input
test my script date <"01/01/1970 00:00:00" - i skip vbscript ms = parseJSONdate ("/ Date (-2209172400000 + 0300) /", utcOffset ) ' "01/01/1970 00:00:00" (original - Fri Dec 29 1899 23:30:17 GMT + 0230 (Moscow, standard time )
date >"01/01/1970 00:00:00" vbscript - parseJSONdate ("/ Date (1533416400000 + 0300) /", utcOffset) = "2018-08-05 00:00:00" 'correct JScript
var f = new Date(1533416400000 + 0300) = Sun Aug 05 2018 00:00:00 GMT+0300 'correct document.write (f.getMonth()+1+"/"+f.getDate()+"/"+f.getFullYear() +" " + f.getHours()+":"+f.getMinutes()+":"+f.getSeconds() +"\n
") = 8/5/2018 0:0:0 'correct
in general, it works of course, but the code is not beautiful, you have more experience - maybe you can offer your own version for your library, taking into account GMT