Concurrent program parameter of date type gave error ORA-01843: not a valid month

Question

I have 2 parameters in my report Query of datatype date, I have selected the fnd_standard_date as value set, here is my query

Select ordered_date, order_number, customer_id
From order_tbl xx       
Where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number
 and xx.ordered_date between NVL(trunc(TO_DATE(:P_FROM_DATE, 'YYYY/MM/DD HH24:MI:SS')), xx.ORDERED_DATE) and NVL(trunc(TO_DATE(:P_TO_DATE, 'YYYY/MM/DD HH24:MI:SS'), xx.ORDERED_DATE)
    AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id>

In SQLDeveloper the result is ok, but in Oracle apps, I am facing

java.sql.SQLDataException: ORA-01843: not a valid month

What value set can I use for this parameter?
And what format can I pass?

Answer 1

If your parameters are already DATEs as you say, then you should not be calling TO_DATE() for them. That will do an implicit conversion of the date to a string using the session's NLS_DATE_FORMAT, and then try to convert that string back into a date using the format model you have specified.

That will work if NLS_DATE_FORMAT happens to match what you specified, but you should not rely on that being the case, and should not do implicit conversions; and you're doing more work than you need to anyway.

So just simplify it to:

Select ordered_date, order_number, customer_id
From order_tbl xx       
Where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number
and xx.ordered_date between NVL(trunc(:P_FROM_DATE), xx.ORDERED_DATE)
   and NVL(trunc(:P_TO_DATE), xx.ORDERED_DATE)
AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id>

If you're running it from SQL Developer with P_DATE_FROM and _TO declared as strings via the variable command, then you will need to use TO_DATE() of course. The two situations are not the same.

Answer 2

Thank you very much!

The problem is with xml publisher / ebs concurrent parameters, just did the following and solved.

1. used the canonical_to_date in query

   Where trunc(xx.ordered_date) between NVL(trunc(fnd_date.canonical_to_date(:P_FROM_DATE)), xx.ORDERED_DATE) and NVL(trunc(fnd_date.canonical_to_date(:P_TO_DATE)), xx.ORDERED_DATE)

2. used the FND_STANDARD_DATE value set