ORA-01830: date format picture ends before converting entire input string error

Question

 INSERT INTO TAB_THD_ATTND_EVENTS(
      ATND_EVNT_INDEXNO,            ATND_EVNT_USERID,             ATND_EVNT_USERNAME,           ATND_EVNT_DT,
      ATND_EVNT_ENEX_TYPE,          ATTND_EVNT_MSTR_CNTRLID,      ATTND_EVNT_DOOR_CNTRLID,      ATTND_EVNT_SPCL_FNCTNID,
      ATTND_EVNT_LEAVE_DT,          ATTND_EVNT_INSERT_DT,         ATTND_EVNT_PRCS_FLAG,         ATTND_EVNT_PRCS_DT,
      CR_DATE
 )
 SELECT 
      X.ATND_EVNT_INDEXNO,          X.ATND_EVNT_USERID,           X.ATND_EVNT_USERNAME,         TO_DATE(X.ATND_EVNT_DT,'DD/MM/YYYY HH24:MI:SS'),
      X.ATND_EVNT_ENEX_TYPE,        X.ATTND_EVNT_MSTR_CNTRLID,    X.ATTND_EVNT_DOOR_CNTRLID,    X.ATTND_EVNT_SPCL_FNCTNID,
      X.ATTND_EVNT_LEAVE_DT,        TO_DATE(X.ATTND_EVNT_INSERT_DT,'MM/DD/YYYY HH24:MI:SS'),    'N',      '',
      SYSDATE 
 FROM TAB_TDL_ATTND_UPLOAD_TEMP T,
      (XMLTABLE('/DocumentElement/event-ta' PASSING T.ATTND_DATA_XML COLUMNS
                     ATND_EVNT_INDEXNO NUMBER PATH './IndexNo',
                     ATND_EVNT_USERID NUMBER PATH './UserID',
                     ATND_EVNT_USERNAME VARCHAR2(100) PATH './UserName',
                     ATND_EVNT_DT DATE PATH './EventDateTime',
                     ATND_EVNT_ENEX_TYPE NUMBER PATH './EntryExitType',
                     ATTND_EVNT_MSTR_CNTRLID NUMBER PATH './MasterControllerID',
                     ATTND_EVNT_DOOR_CNTRLID NUMBER PATH './DoorControllerID',
                     ATTND_EVNT_SPCL_FNCTNID NUMBER PATH './SpecialFunctionID',
                     ATTND_EVNT_LEAVE_DT DATE PATH './LeaveDT',
                     ATTND_EVNT_INSERT_DT DATE PATH './IDateTime')) X
 WHERE T.ATTND_UPLOAD_NO = P_SEQNO;

Above written is my Insert Query in PLSQL, and given below is the XML String Data that I'm trying to insert, but I'm getting the error mentioned. Can anyone help me out with this?

<event-ta>
<IndexNo>85672</IndexNo>
<UserID>1001</UserID>
<UserName>Testing Data</UserName>
<EventDateTime>17/04/2023 08:08:50</EventDateTime>
<EntryExitType>0</EntryExitType>
<MasterControllerID>34</MasterControllerID>
<DoorControllerID>1</DoorControllerID>
<SpecialFunctionID>0</SpecialFunctionID>
<LeaveDT/>
<IDateTime>04/17/2023 08:08:53</IDateTime>
</event-ta>

X.ATND_EVNT_DT and X.ATTND_EVNT_INSERT_DT are, apparently, VARCHAR2 datatype columns and are supposed to contain value in format you specified, and that is DD/MM/YYYY HH24:MI:SS (and MM/DD/YYYY HH24:MI:SS). Oracle says that column value doesn't really follow that format. Generally speaking, you should store date values into DATE datatype columns to avoid such problems. What now? Find values that differ from format you specified and fix them. — Littlefoot, Apr 19 '23 at 09:33
What has this got to do with VB.NET or ASP.NET? Unless the problem is related to those things, the fact that the app in which the problem occurs is created with those things is irrelevant. — jmcilhinney, Apr 19 '23 at 11:54
Would it be possible to use ISO 8601 and *only* ISO 8601 for the date format in the XML? [What is the correct format to use for Date/Time in an XML file](https://stackoverflow.com/a/254803/1115360). Anyway, can you reduce the number of elements it is trying to read until it stops giving an error? Then you know which part was going wrong. — Andrew Morton, Apr 19 '23 at 14:04
Can you reduce your code in removing unnecessary code ? Keep only code that reproduces same error ! And remove vb.net from tag's list ! Can you reproduce same error in removing INSERT ? If yes, remove INSERT part from your question. Make your question the simplest possible. When you have reducted your code, can you send us (without INSERT), can you display what is displaying your SELECT in removing TO_DATE conversion so we can see TO_DATE input value ? — schlebe, Apr 22 '23 at 06:38
"ATTND_EVNT_INSERT_DT DATE" should return a date, right? If so, why would you to a TO_DATE on it? Don't TO_DATE a date. TO_DATE strings to make them dates. — Paul W, Apr 22 '23 at 16:47

score 0 · Accepted Answer · answered Apr 22 '23 at 14:14

To answer to your question that is "Why this", the problem occurs because your definition of ATTND_EVNT_INSERT_DT as DATE is incorrect because element date format is incorrect (certainly distinct from default Oracle Data Format).

To solve your problem, I propose to use following reducted command (without INSERT because I don't think that problem is in INSERT Sql statement).

SELECT TO_DATE(X.ATND_EVNT_DT        ,'DD/MM/YYYY HH24:MI:SS')
      ,TO_DATE(X.ATTND_EVNT_INSERT_DT,'MM/DD/YYYY HH24:MI:SS')
  FROM TAB_TDL_ATTND_UPLOAD_TEMP T
    ,(XMLTABLE('/DocumentElement/event-ta' 
        PASSING T.ATTND_DATA_XML 
        COLUMNS ATND_EVNT_DT         VARCHAR2(20) PATH './EventDateTime'
               ,ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime'
              )
     ) X
 WHERE T.ATTND_UPLOAD_NO = P_SEQNO;

I have only replace DATE in XML part by VARCHAR2(20).

If this command doesn't work, inform us about what is returning following command

SELECT X.ATND_EVNT_DT
      ,X.ATTND_EVNT_INSERT_DT
  FROM TAB_TDL_ATTND_UPLOAD_TEMP T
    ,(XMLTABLE('/DocumentElement/event-ta' 
        PASSING T.ATTND_DATA_XML 
        COLUMNS ATND_EVNT_DT         VARCHAR2(20) PATH './EventDateTime'
               ,ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime'
              )
     ) X
 WHERE T.ATTND_UPLOAD_NO = P_SEQNO;

ORA-01830: date format picture ends before converting entire input string error

1 Answers1