BI Publisher SQL - Date parameter wrong data

Question

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

usersrekan · Answer

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

Senthilrajan V · Answer

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.