Oracle 21c

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