[Solved] where clause with date range give strange result

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 … Read more