0

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.
MT0
  • 143,790
  • 11
  • 59
  • 117
NerdFredrick
  • 71
  • 4
  • The main problem is storing a DATE as of string type, so we can encounter that determined oracle error for multiple rows due to this fact. Do you have an opportunity to transfer data step-by-step to a DATE type column through starting with more recoverible ones ? – Barbaros Özhan May 23 '22 at 11:25
  • I am afraid we can do that because this is a seeded oracle table in cloud model we don't have that privilege. – NerdFredrick May 23 '22 at 12:04

1 Answers1

0

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

EdHayes3
  • 1,777
  • 2
  • 16
  • 31