where clause with date range give strange result

Question

In an xml publisher report I have the following where clause:

    where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number  
     and 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)  
    AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id

but its behavior is strange, with date range its result is ok, but with out date range its result is NULL.

I tried the following too:

where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number  
 and ( NVL(TRUNC(fnd_date.canonical_to_date(:P_FROM_DATE)), xx.ORDERED_DATE) > xx.ordered_date  
       or  NVL(TRUNC(fnd_date.canonical_to_date(:P_TO_DATE)), xx.ORDERED_DATE) < xx.ordered_date )  
AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id 

in concurrent program the dates parameters has FND_STANDARD_DATE.

Answer 1

there are many ways of skinning this particular cat, this is just one method. By the way in your example you gave a date of 31st April, this does not exists.

SELECT TO_Char(ordered_date,'DD-MON-YYYY') as ordered_date,
    order_number, customer_name   
FROM order_tbl
WHERE NVL(:P_ORDER_NUMBER, order_number) = order_number
  AND ordered_date between NVL(TO_DATE(:P_FROM_DATE,'DD-MON-YYYY'),TO_DATE('01-MAR-1900','DD-MON-YYYY')) and NVL(to_date(:P_TO_DATE,'DD-MON-YYYY'),TO_DATE('31-DEC-2100','DD-MON-YYYY'))   
  AND NVL(:P_CUSTOMER_NAME, customer_name) = customer_name