Business Intelligence

Get Involved. Join the Conversation.

Topic

    Srekan
    Date with timestamp parameter in BI publisher report 12c
    Topic posted October 11, 2019 by Srekan Red Ribbon: 250+ Points, last edited October 11, 2019, tagged BI Publisher, Business Intelligence 
    25 Views
    Title:
    Date with timestamp parameter in BI publisher report 12c
    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