An error ORA-01843 occured while performing SQL prompt insert in Cognos

Technote (troubleshooting)


Attempts to insert sql prompt date format result in error when using IBM Cognos 8.


The error snapshot shows error ORA-01843, which is due to invalid month. The user might be entering a month portion of the date which was not a valid month format.

Resolving the problem

Based on the SQL insertable statement, the following format are updated for the to_date function

SQL syntax to_date(#prompt('pt_start')#,'MON DD, YYYY HH:MI AM') and "HSIA_CELL_TAB_VIEW"."TSTAMP"<=to_date(#prompt('pt_end')#,'MON DD, YYYY HH:MI AM')

But from the SQL_Insertable_query_from_Generated_SQL_MDX.txt (after the prompt was issued), these date entries as shown, and the format of MON DD YYY HH:MI AM from the above SQL insertable statement does not match the 2012-04-06T00:00.000 format.

SQL syntax 2012-04-06T00:00:00.000','MON DD, YYYY HH:MI AM') and "HSIA_CELL_TAB_VIEW"."TSTAMP"<=to_date('2012-04-06T00:00:00.000','MON DD, YYYY HH:MI AM')

Based on the SQL oracle explanation of the to_date function:-

MON Means Abbreviated name of the month, JAN, FEB,MAR,APR,MAY,JUN,JUL,AUG,Sep,OCT,Nov,Dec
DD Day of the month(1-31)
HH Hour of the day 1-12
YYYY 4 digit of the year
AM Meridian Indicator
MI (Minute 0-59)

To resolve this error:-

Please change the SQL_insertable statement to have the correct date format mask to match the value from SQL_Insertable_query_from_Generated_SQL_MDX.txt

Product Alias/Synonym


Rate this page:

(0 users)Average rating

Document information

More support for:

Tivoli Netcool Performance Manager for Wireless

Software version:

1.2.1, 1.3

Operating system(s):

AIX, Linux, Solaris

Software edition:

All Editions

Reference #:


Modified date:


Translate my page

Machine Translation

Content navigation