By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- change local time zone to demonstrate later conversion, as this server is currently GMT
alter session set time_zone = 'America/New_York'
CREATE TABLE MATERIAL_T (MESSAGE_VALUE VARCHAR2(4000) CHECK (MESSAGE_VALUE IS JSON));
INSERT INTO MATERIAL_T (MESSAGE_VALUE) VALUES ('{"_id":"58093","createdDate":"2023-12-08T12:25:36.686Z"}');
1 rows affected
CREATE TABLE MATERIAL_JSON_DECODE ( ID NUMBER(5), CREATED_DATE DATE );
-- original code - works as-is with default NLS settings... sort of...
INSERT INTO MATERIAL_JSON_DECODE(ID , CREATED_DATE)
WITH CODES AS
(
SELECT ID,
CAST(TO_TIMESTAMP_TZ(createdDate, 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') AT LOCAL AS DATE)createdDate
FROM
(
SELECT DISTINCT
ID,createdDate
FROM
MATERIAL_T D,
JSON_TABLE
(
D.MESSAGE_VALUE, '$' COLUMNS
(
ID VARCHAR2(6) PATH '$._id',
createdDate VARCHAR2(100) PATH '$.createdDate'
)
)
)
)
SELECT ID,TO_DATE(CREATEDDATE ,'DD-MON-YYYY HH24:MI:SS') AS CREATED_DATE FROM CODES ;
1 rows affected
-- ... but the default NLS means the conversion ends up with an implicit 2-digit year, which becomes 0023 not 2023, and sets the time to midnight
select id, to_char(created_date, 'YYYY-MM-DD HH24:MI:SS') as created_date from MATERIAL_JSON_DECODE
| ID | CREATED_DATE |
|---|---|
| 58093 | 0023-12-08 00:00:00 |
rollback;
-- original JSON table getting string
SELECT DISTINCT
ID,createdDate
FROM
MATERIAL_T D,
JSON_TABLE
(
D.MESSAGE_VALUE, '$' COLUMNS
(
ID VARCHAR2(6) PATH '$._id',
createdDate VARCHAR2(100) PATH '$.createdDate'
)
)
| ID | CREATEDDATE |
|---|---|
| 58093 | 2023-12-08T12:25:36.686Z |
-- modified JSON table getting timestamp
SELECT DISTINCT
ID,createdDate
FROM
MATERIAL_T D
CROSS APPLY JSON_TABLE
(
D.MESSAGE_VALUE, '$' COLUMNS
(
ID VARCHAR2(6) PATH '$._id',
createdDate TIMESTAMP WITH TIME ZONE PATH '$.createdDate'
)
)
| ID | CREATEDDATE |
|---|---|
| 58093 | 08-DEC-23 12.25.36.686000 +00:00 |
INSERT INTO MATERIAL_JSON_DECODE(ID , CREATED_DATE)
SELECT DISTINCT ID, CAST(createdDate AT LOCAL AS DATE)
FROM MATERIAL_T D
CROSS APPLY JSON_TABLE
(
D.MESSAGE_VALUE, '$'
COLUMNS
(
ID VARCHAR2(6) PATH '$._id',
createdDate TIMESTAMP WITH TIME ZONE PATH '$.createdDate'
)
)
1 rows affected
select id, to_char(created_date, 'YYYY-MM-DD HH24:MI:SS') as created_date from MATERIAL_JSON_DECODE
| ID | CREATED_DATE |
|---|---|
| 58093 | 2023-12-08 07:25:36 |