Getting Oracle Error While trying to convert varchar2 column and retrieve data using BIP report

Question

We have column (field) attribute14 in the oracle table which is of data type VARCHAR2 and is storing date in format '20-MAY-22' (DD-MON-YY).

We have a requirement to develop a BIP report in oracle fusion and get output based on the parameter date passed & if no parameter is based then all the records.

But as we all know any date parameter in the oracle Data set or BIP report will be in the below format.

Date Parameter: '2021-12-05T19:00:00.000-05:00'

And upon trying the below query when the parameter is passed the following error.

select *
from   ab cet
where  1=1
AND    TO_DATE(cet. attribute14, DD-MON-YY NLS_date_language=AMERICAN")
         IN ( nvl(:void_date.TO_DATE(cet.attribute14, DD-MON-YY.NLS_date_language=AMERICAN")))

The below error comes when the parameter value is passed.

Error: ORA-01847: day of the month must be between 1 and the last day of the month.

Answer 1

You need to use single quotes around the date format. Seems your language is also incorrect. Details can be read in the Oracle documentation. Here is a simple example.

SELECT TO_DATE(
    '20-MAY-22',
    'DD-MON-YY',
    'NLS_DATE_LANGUAGE = American')
FROM DUAL;

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm