oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01843: not a valid month in BI Publisher
Whenever we use TO_DATE in BIP Data Model (Consider the sample below query) – You will receive an exception.
SELECT
GLD.FROM_CURRENCY ,
GLD.TO_CURRENCY,
GLD.CONVERSION_DATE,
GLD.CONVERSION_TYPE,
GLD.CONVERSION_RATE
FROM GL_DAILY_RATES GLD
WHERE 1=1
and GLD.FROM_CURRENCY ='USD'
AND GLD.TO_CURRENCY ='INR'
AND CONVERSION_TYPE ='Corporate'
AND CONVERSION_DATE = TO_DATE ( '18-APR-2021','DD-MON-YYYY')
Solution :
TO_DATE ( ’18-APR-2021′,’DD-MON-YYYY’ ,’NLS_DATE_LANGUAGE = American’)
SELECT
GLD.FROM_CURRENCY ,
GLD.TO_CURRENCY,
GLD.CONVERSION_DATE,
GLD.CONVERSION_TYPE,
GLD.CONVERSION_RATE
FROM GL_DAILY_RATES GLD
WHERE 1=1
and GLD.FROM_CURRENCY ='USD'
AND GLD.TO_CURRENCY ='INR'
AND CONVERSION_TYPE ='Corporate'
AND CONVERSION_DATE = TO_DATE ( '18-APR-2021','DD-MON-YYYY' ,'NLS_DATE_LANGUAGE = American')
Comments |0|
Category: OracleSaaS