Default date format from system.date.jsonEncode is not compatible with MS-SQL's OPENJSON

Ignition
1

When use system.date.jsonEncode to encode a java.util.Date into a json string and put it into an MS-SQSL db, I cannot read the field back out of the string within SQL using the OPENJSON command.

Start with a simple dictionary with dates, converted to a json string:

value = {
  "startTime": system.date.getDate(2023, 5, 29),
  "endTime": system.date.getDate(2023, 5, 19)
}

jsonStr = system.util.jsonEncode(value)

Result:

{"startTime":"Thu Jun 29 00:00:00 UTC 2023","endTime":"Mon Jun 19 00:00:00 UTC 2023"}

Literal string on the SQL side for simplicity:

Declare @JSON varchar(max) = '{"startTime": "Thu Jun 29 00:00:00 UTC 2023","endTime": "Mon Jun 19 00:00:00 UTC 2023"}'

SELECT * FROM  
 OPENJSON (@JSON )  
WITH (   
              startTime  datetime2 '$.startTime',  
              endTime    datetime2 '$.endTime'
 )

Result:

Conversion failed when converting date and/or time from character string.

Is there a simple workaround for this short of writing my own version of system.util.jsonEncode()?

1 Like
2

A terrible workaround but perhaps system.dataset.formatDates could be useful? Convert the system.date objects into strings (formatted to ISO8601 compliant format, the only acceptable format for machine-to-machine text messages) before the json.Encode.

1 Like
3

That's the 'rewrite system.util.jsonEncode()' that I'm trying to avoid.
Hoping there's a way to override the java.util.Date._ str__() or perhaps use an existing open source function.

4

Convert them to Instant before encoding:

value = {
  "startTime": system.date.getDate(2023, 5, 29).toInstant(),
  "endTime": system.date.getDate(2023, 5, 19).toInstant()
}

jsonStr = system.util.jsonEncode(value)

edit: FWIW, this is barely any different from what @Tim_Carlton suggested.

3 Likes
5

Yes, thank you both. The more complicated story here is that the original json comes from a third-party system. We do display it in a table in Ignition, so we do have it in a dataset and can manipulate the fields manually, but we are not really in control of the fields or their data types.

We could also write a routine to drill down through the array/dictionary fields to look for data items with the type name 'datetime' and convert the type of each one before calling encode, but I am hoping for something a little more pythonic.

6

My guess is that system.dataset.formatDates does Exactly that, except for datasets instead of dictionaries.

1 Like
7

Oh! Let me clean my glasses - I read that as system.date.format(date, format). Yes, thank you!

1 Like