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

When use 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":, 5, 29),
  "endTime":, 5, 19)

jsonStr = system.util.jsonEncode(value)


{"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"}'

WITH (   
              startTime  datetime2 '$.startTime',  
              endTime    datetime2 '$.endTime'


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

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

1 Like

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.

Convert them to Instant before encoding:

value = {
  "startTime":, 5, 29).toInstant(),
  "endTime":, 5, 19).toInstant()

jsonStr = system.util.jsonEncode(value)

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


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.

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

1 Like

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

1 Like