Date with timestamp parameter in BI publisher report 12c

usersrekan

Summary

datetime parameters passing in BI publisher report

Content

Hi All,

i am creating datewithtime parameterized report.

i have database time column(xx_time) and the data as in the format of  10-OCT-19 09.45.22.846000000 AM  -------------> it is in (CST) format. But i have to show in EST format. For that i have used CAST(FROM_TZ(CAST(xx_ba.xx_time AS TIMESTAMP), 'CST') at TIME zone 'America/New_York' AS DATE) AS TIMEST. its working here.

now i have to fetch records on TIMEST (which is calcualted column)  between start date and enddate parameters. whereas parameters datetime format as 10/13/2019 10:30:15 AM (MM/dd/yyyy hh:mi:ss AM).

i have used query as

SELECT xx_id,
    xx_time,
    TIMEST
    
  FROM
    (SELECT xx_ba.xx_id,
      xx_ba.xx_time,
  CAST(FROM_TZ(CAST(xx_ba.xx_time AS TIMESTAMP), 'CST') at TIME zone 'America/New_York' AS DATE) AS TIMEST
      
    FROM xx_ba xx_ba
    )
  WHERE TIMEST >= to_date(to_char(:startDate, 'MM/dd/yyyy hh:mi:ss AM'),'MM/dd/yyyy hh:mi:ss AM')
  AND TIMEST <= to_date(to_char(:endDate, 'MM/dd/yyyy hh:mi:ss AM'), 'MM/dd/yyyy hh:mi:ss AM')

throwing error

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

 

tried different options but no luck, Any help on this really appreciated.

Regards,

Joes