Date parameters in Oracle Report Query

1.If the date is already in the format 'mm/dd/yyyy' in database, then at the time of defining date as parameter, no need to write as;

 XXX_TRX_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'mm/dd/yyyy'),XXX_TRX_DATE)
AND NVL(TO_DATE(:P_DATE_TO,'mm/dd/yyyy'),XXX_TRX_DATE)

instead following can be written:

 XXX_TRX_DATE BETWEEN NVL(:P_DATE_FROM, XXX_TRX_DATE)
AND NVL(:P_DATE_TO, XXX_TRX_DATE)

No need to convert to TO_DATE format, as its already in 'mm/dd/yyyy' format.
If its not in above format, then we need to convert into TO_DATE format.


2. While executing the parameter date query, always change the Type of the bind variables to 'Date' for the date parameters in TOAD as shown below.

3. If you are creating xml data template for the parameter query. Always  define the parameter datatype as 'Date' as shown below;

<parameter name="p_XXX_date" dataType="Date" />