Json Dates - revisited

[spl]

This is a longer post than most.

Back to my DataTable output:

Code Select Expand

[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

Code Select Expand

[
    {
        "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

Code Select Expand

[
    {
        "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

Code Select Expand

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