Hi,
I was wondering whether anyone has experience of using JSON_OBJECT SQL functions on 12.2.0.1.0, mainly surrounding using DATE fields in a query.
I have provided a simplified example to demonstrate the issue:
We have a simple table of the following structure:
PUBLIC_HOLIDAYS:
Name Null Type
------------ -------- ------------
PH_SEQ NOT NULL NUMBER(10)
HOLIDAY_DATE NOT NULL DATE
DESCRIPTION VARCHAR2(50)
Setting the session settings as follows to demonstrate the problem:
alter session set nls_timestamp_format = 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"';
alter session set nls_date_format = 'DD-MON-YYYY';
When querying the data using a standard select, as per:
select cast (holiday_date as timestamp)
,holiday_date
from public_holidays;
The output is shown as follows (this is as I would expect):
2020-05-04T00:00:00.000Z 04-MAY-2020
2020-05-25T00:00:00.000Z 25-MAY-2020
However when applying a JSON_OBJECT function to convert to JSON, the dates are manipulated as follows e.g. the Z is now omitted from both:
select JSON_OBJECT('dateWithTz' VALUE cast (holiday_date as timestamp),
'regDate' VALUE holiday_date)
from public_holidays;
{"dateWithTz":"2020-05-04T00:00:00","regDate":"2020-05-04T00:00:00"} {"dateWithTz":"2020-05-25T00:00:00","regDate":"2020-05-25T00:00:00"}
I essentially want to return the date as a JSON object, with the date formatted as per the ISO 8601 format. e.g. 2019-06-20T15:41:17.000Z
The oracle documentation suggests this is how it should work; however as above we see the 'Z' omitted e.g. Timezone for a zero offset. Would this be expected?
FYI the oracle documentation is:https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-C0F8F837-EE36-4EDD-9261-6E8A9…
"A DATE or TIMESTAMP value is converted to ISO 8601 format, and the result is enclosed in double quotation marks (")."
The only workaround I can see is TO_CHAR the date and apply the required format e.g. YYYY-MM-DD"T"HH24:MI:SS"Z"; however I was hoping this could have been handled by default.
If anyone has any comments it would be appreciated.
Kind Regards
David