Business Intelligence

Get Involved. Join the Conversation.

Topic

    Srekan
    BI Publisher SQL - Date parameter wrong data
    Topic posted October 2, 2019 by Srekan Red Ribbon: 250+ Points, tagged BI Publisher, Tip 
    62 Views, 2 Comments
    Title:
    BI Publisher SQL - Date parameter wrong data
    Content:

    Hi All,

    i  am trying to execute report with data parameters. In oracle database date column data format is 01-OCT-19 01.40.09.857000000 AM.

    whereas in parameter the date is 10/03/2019 (i.e MM/DD/YYYY). Looks like my parameter data is not comparing properly with database format data. please let me know do i need to any conversion? Thanks in advance.

    example:


    SELECT ID,orderdate FROM order
    where orderdate >= '10/02/2019' AND orderdate <= '10/03/2019' order by orderdate ASC

    i have to get results for  date 10/02 and 10/03 records only with timestamps.

    Regards,

    Joes

    Comment

     

    • Senthilrajan Vaithianathan

      We use data parameters and is working fine, try to define the Data model - Parameter 'Data Type' for 'order date' as 'Date'. You may get a calendar option in report.

      • Srekan

        Hi  Senthilrajan Vaithianathan

        i have used already datatype as Date only and date format i gave MM/dd/yyyy.

        But i guess my issue in my sql, i have fixed my sql. now

        the way i am comparing date as

        to_char(orderdate, 'MM/dd/yyyy') >=  to_char(:startDate, 'MM/dd/yyyy')
             AND to_char(orderdate, 'MM/dd/yyyy') <= to_char(:endDate, 'MM/dd/yyyy')

        once i use above code its working fine for me.

        previously i am not used to_char function.

        Thank you for your help.

        Regards,

        Joe