Incorrect Result FlexibleSearch Query

[Former Member]

Hi Experts,

I'm trying to run the below query but it's not giving me the expected result.

SELECT DISTINCT to_char({cst:creationtime},'dd/MM/yyyy') FROM {CsTicket AS cst} WHERE to_char({cst:creationtime},'yyyy/MM/dd') BETWEEN '2019/01/08' AND '2019/01/09'

Can you please help me in finding the issue?

Hybris:6.2 DB: Oracle

[Former Member]

The data that it fetches from the DB is not in the date range.

[arvind-kumar_avinash · Accepted solution]

Please try this:

 SELECT DISTINCT TO_CHAR({cst:creationtime},'DD/MM/YYYY') FROM {CsTicket AS cst} WHERE {cst:creationtime} BETWEEN TO_DATE('2019/01/08','YYYY/MM/DD') AND TO_DATE('2019/01/09','YYYY/MM/DD')

The reason why your query failed to fetch correct data is because you compared two strings and not two dates for calculating the range.

If you need dates in date format instead of string format, you can further change my query to:

 SELECT DISTINCT TO_DATE({cst:creationtime},'DD/MM/YYYY') FROM {CsTicket AS cst} WHERE {cst:creationtime} BETWEEN TO_DATE('2019/01/08','YYYY/MM/DD') AND TO_DATE('2019/01/09','YYYY/MM/DD')

[Former Member · Accepted solution]

Hi Thanks for the reply. I actually figured it out now.

Another question, when I convert this to FlexibleSearchQuery where the first date is the start date and the second date is end date, Do I need to keep them in quotes?

BETWEEN to_date('?startDate','dd/MM/yyyy') AND to_date('?endDate','dd/MM/yyyy')

or

BETWEEN to_date(?startDate,'dd/MM/yyyy') AND to_date(?endDate,'dd/MM/yyyy') will work fine?

[arvind-kumar_avinash · Accepted solution]

You are most welcome.

I suggest using the whole query as a string e.g. https://help.hybris.com/1811/hcd/8bc399c186691014b8fce25e96614547.html#topic_owf_ltp_g5