This is a longer post than most.
Back to my DataTable output:
[void]$table.Rows.Add("Brian", "55000","04/17/1999" ,"UK",1)
[void]$table.Rows.Add("Joe", "34000","06/06/2006", "Canada",0)
[void]$table.Rows.Add("Sam", "107000","07/11/2001", "Canada",1)
[void]$table.Rows.Add("Eric", "25000","03/20/2007", "USA",0)
These were typed columns so the dates were DateTime. Assume it gets persisted as Json via PS or .NET [I don't think the WB Json Extender persists, just reads] You Get
[
{
"Name": "Brian",
"Salary": 55000,
"DOB": "\/Date(924325200000)\/",
"Location": "UK",
"Salaried": true
},
{
"Name": "Joe",
"Salary": 34000,
"DOB": "\/Date(1149566400000)\/",
"Location": "Canada",
"Salaried": false
},
{
"Name": "Sam",
"Salary": 107000,
"DOB": "\/Date(994827600000)\/",
"Location": "Canada",
"Salaried": true
},
{
"Name": "Eric",
"Salary": 25000,
"DOB": "\/Date(1174363200000)\/",
"Location": "USA",
"Salaried": false
}
]
and the problem here is Json dates in \/ format are UTC dates, and retrieving them back from the json is a pain. Now, while I could write modified code to persist as
[
{
"Name": "Brian",
"Salary": 55000,
"DOB": "04/17/1999",
"Location": "UK",
"Salaried": true
},
{
"Name": "Joe",
"Salary": 34000,
"DOB": "06/06/2006",
"Location": "Canada",
"Salaried": false
},
{
"Name": "Sam",
"Salary": 107000,
"DOB": "07/11/2001",
"Location": "Canada",
"Salaried": true
},
{
"Name": "Eric",
"Salary": 25000,
"DOB": "03/20/2007",
"Location": "USA",
"Salaried": false
}
]
still does not preclude that one might have to parse a Json file in the original format. Fortunately WB makes it easy to return the Json dates to the original form. For the sake of simplicity, assume you get a Json file like the first shot above. You read it, determine it is a valid file and go about parsing the values. Correctly converting the date types, turns out it was something I brought up in May 2024 and Tony responded with some WB magic. The code below processes the Json-type dates from the first section. Again, for simplicity sake I put them in a list and foreach...next. But the data returns correctly as placed in the original DataTable
jsDates = "\/Date(924325200000)\/,\/Date(1149566400000)\/,\/Date(994827600000)\/,\/Date(1174363200000)\/"
cnt = ItemCount(jsDates,",")
dates=""
For i= 1 To cnt
jsDate = ItemExtract(i,jsDates,",")
DateTicks = Strsub(jsDate,8,Strlen(jsDate) -10)
Offset = DateTicks/1000
UTC = TimeAdd("1970:01:01:00:00:00","0000:00:00:00:00:":Offset)
;; Assuming time is UTC...
EST = TimeZoneConvert(UTC, "UTC", "Eastern Standard Time")
Formatted = TimeFormat(EST, "MM/dd/yyyy")
dates = dates:Formatted:@LF
Next
Message("Json Converted Dates", dates)
Exit