what is the best way to get result using the below query

Question

Following query does not show any output but it does not have any errors -

select inv_id
from invoice_table 
where 
INVOICE_DATE BETWEEN to_char(P_INVOICE_START_DATE,'DD-MON-YYYY')AND to_char(P_INVOICE_END_DATE,'DD-MON-YYYY');

Where did I go wrong and how can i improve it?

Is column "invoice_date" of type DATE or VARCHAR2?, is parameter "P_INVOICE_START_DATE" and "P_INVOICE_END_DATE" of type DATE or VARCHAR2? — alvalongo, Aug 24 '18 at 14:40

score 0 · Answer 1 · answered Aug 29 '18 at 07:19

I managed to find solution all parameters are of type Varchar2 and used the FND_DATE.CANONICAL_TO_DATE function as below

   `***...AND T.INVOICE_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(P_INVOICE_START_DATE) AND FND_DATE.CANONICAL_TO_DATE(P_INVOICE_END_DATE);***`

what is the best way to get result using the below query

1 Answers1