# Default date format from system.date.jsonEncode is not compatible with MS-SQL's OPENJSON **URL:** \ **Category:** Ignition\ **Tags:** scripting\ **Created:** [September 26, 2023, 4:34pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662 "2023-09-26T16:34:53Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![james.vosler](https://avatars.discourse-cdn.com/v4/letter/j/8797f3/32.png) [@james.vosler](https://forum.inductiveautomation.com/u/james.vosler)\ **Post date:** [September 26, 2023, 4:34pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/1 "2023-09-26T16:34:53Z")
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: ```auto 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: ```auto 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()? ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [September 26, 2023, 5:09pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/2 "2023-09-26T17:09:03Z")
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. ---
**Author:** ![james.vosler](https://avatars.discourse-cdn.com/v4/letter/j/8797f3/32.png) [@james.vosler](https://forum.inductiveautomation.com/u/james.vosler)\ **Post date:** [September 26, 2023, 8:29pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/3 "2023-09-26T20:29:27Z")
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. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [September 26, 2023, 8:35pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/4 "2023-09-26T20:35:05Z")
Convert them to Instant before encoding: ```auto 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. ---
**Author:** ![james.vosler](https://avatars.discourse-cdn.com/v4/letter/j/8797f3/32.png) [@james.vosler](https://forum.inductiveautomation.com/u/james.vosler)\ **Post date:** [September 28, 2023, 8:59pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/5 "2023-09-28T20:59:14Z")
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. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [September 28, 2023, 9:50pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/6 "2023-09-28T21:50:51Z")
> [@james.vosler](#): > > 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 My guess is that `system.dataset.formatDates` does _Exactly_ that, except for datasets instead of dictionaries. ---
**Author:** ![james.vosler](https://avatars.discourse-cdn.com/v4/letter/j/8797f3/32.png) [@james.vosler](https://forum.inductiveautomation.com/u/james.vosler)\ **Post date:** [September 28, 2023, 10:11pm UTC](https://forum.inductiveautomation.com/t/default-date-format-from-system-date-jsonencode-is-not-compatible-with-ms-sqls-openjson/79662/7 "2023-09-28T22:11:44Z")
Oh! Let me clean my glasses - I read that as **system.date.format(date, format)**. Yes, thank you!