How to pass a date parameter from report builder query designer to oracle database

Question

i'm using report builder 3.0 connected to oracle database. i'm trying to pass a date parameter in the query with no success, i don't know the exact syntax. I've tried :

SELECT * 
FROM igeneral.GCL_CLAIMS
WHERE CREATED_BY IN (:CREATED_BY) AND CLAIM_YEAR IN(:UW_YEAR)  
  AND (Trunc(LOSS_DATE,'mm/dd/yyyy') BETWEEN to_char(':From', 'mm/dd/yyyy') 
  AND to_char('To', 'mm/dd/yyyy'))

i got this error: ORA-01036: illegal variable name/number

also i've tried this:

SELECT * 
FROM igeneral.GCL_CLAIMS 
WHERE CREATED_BY IN (:CREATED_BY) AND CLAIM_YEAR IN(:UW_YEAR)  
  AND (LOSS_DATE BETWEEN ':From' AND ':To') 

i got this error: ORA-01036: illegal variable name/number

thanks

Answer 1

The solution depends on the data type of the from and to parameter.

If your parameter is DATE datatype, use to_char to conver date into a literal of the required format you want to display. Else, if the parameter is VARCHAR2 datatype, use to_date to convert the date literal to date for comparision.

In your case, it is probable that LOSS_DATE column is a DATE, and your parameters are literals, so use `TO_DATE on the parameters using proper format mask.

LOSS_DATE BETWEEN to_date(':From', 'mm/dd/yyyy') AND to_date('To', 'mm/dd/yyyy')