Oracle Analytics Cloud and Server

Products Banner

BI Publisher SQL - Date parameter wrong data

Received Response
315
Views
2
Comments

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

Tagged:

Answers

  • 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.

    BIP Date Prompt.png

  • usersrekan
    usersrekan ✭✭✭✭

    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