How to resolve error ORA-01861: literal does not match format string during insertion of JSON data in Oracle table?

Question

I have a table

CREATE TABLE MATERIAL_JSON_DECODE ( ID NUMBER(5), CREATED_DATE DATE );

During insertion , I am getting error ORA-01861: literal does not match format string :

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 ;

MESSAGE_VALUE Column in MATERIAL_T has JSON data :

{"_id":"58093","createdDate":"2023-12-08T12:25:36.686Z"}

Please guide how to resolve this error ?

Answer 1

As well as doing unnecessary conversions, you are relying on implicit conversions, which will sometimes work - and will sometimes appear to work but not give you the expected result. For example with the 'default' NLS settings your code doesn't error, but gives a final result in the year 0023, not 2023, and also loses the time. (See example in the fiddle below.)

You can avoid almost all the conversion though, by telling JSON_TABLE that the value is a timestamp rather than a string - as it's in ISO format it will then do the conversion for you:

    createdDate TIMESTAMP WITH TIME ZONE PATH '$.createdDate'

You then just need to do your time zone adjustment from UTC, and cast to the right data type. You can also simplify your code to remove the extra CTE and inline view along the way. (And move away from the old join syntax using commas...)

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'
  )
)

The result depends on your actual local time zone of course, but if you were in New York that would give you:

ID	CREATED_DATE
58093	2023-12-08 07:25:36

fiddle

Answer 2

You have this:

TO_DATE(CREATEDDATE ,'DD-MON-YYYY HH24:MI:SS')

At this point, CREATEDDATE is already a DATE:

CAST(TO_TIMESTAMP_TZ(createdDate, 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') AT LOCAL AS DATE)createdDate

I don't have Oracle at hand to test but I presume that, since TO_DATE() expects a string, you get your date formatted with the default session/global format. Whenever that format is not 'DD-MON-YYYY HH24:MI:SS', you'll get a crash.

CREATEDDATE is a DATE, and you cast it to DATE in order to insert it in a DATE column. You could fix the redundant cast, but it makes more sense to simply skip it.

Answer 3

If your MESSAGE_VALUE is {"_id":"58093","createdDate":"2023-12-08T12:25:36.686Z"} and your JSON_TABLE defines the createdDate as Varchar2 then it is a string and you can get it as string and transform the string to DATE datatype.

WITH 
    MATERIAL_T AS
        ( Select 1 "ID", '{"_id":"58093","createdDate":"2023-12-08T12:25:36.686Z"}' "MESSAGE_VALUE" From Dual
        ),
    CODES AS 
        ( SELECT  ID, CREATEDDATE "CREATEDDATE_STRING", 
                  To_Date(SubStr(Replace(CREATEDDATE, 'T', ' '), 1, 19),'yyyy-mm-dd hh24:mi:ss') "CREATEDDATE_DATE"
          FROM    ( SELECT DISTINCT D.ID, J.CREATEDDATE
                    FROM    MATERIAL_T D,
                    JSON_TABLE ( D.MESSAGE_VALUE, '$' COLUMNS
                                  ( ID VARCHAR2(6) PATH '$._id',
                                    CREATEDDATE VARCHAR2(100) PATH '$.createdDate'
                                  )   
                               ) J
                  )
        )
--  Main SQL:
SELECT  *
FROM    CODES

/*     R e s u l t :
        ID CREATEDDATE_STRING            CREATEDDATE_DATE
---------- ----------------------------- -----------------
         1 2023-12-08T12:25:36.686Z      08.12.23           */

For your INSERT statement take the Main SQL as:

SELECT  ID, CREATEDDATE_DATE
FROM    CODES ;

Test after insert

SELECT  ID, CREATED_DATE, To_Char(CREATED_DATE, 'dd.mm.yyyy hh24:mi:ss') "DATE_AND_TIME"
FROM    MATERIAL_JSON_DECODE ;
/*
        ID CREATED_ DATE_AND_TIME      
---------- -------- -------------------
         1 08.12.23 08.12.2023 12:25:36   */