How to use Date in Hybris Flexible Search Query?

Question

I am trying to fetch records from hybris using Flexible Search query based on date. I tried to find some resources for the same but none worked out.

Basically, I am trying to find products where modified date is equals to the current date.

My current query is:

Select * from {product as p} where to_char({p.modifiedDate},'dd/mm/yyyy')==to_char('18/04/2017','dd/mm/yyyy')

This is my current query. However, when I run this using HAC, it give me error:

xception message: ORA-00936: missing expression
Exception stacktrace:
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776) oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897) 

Can anyone help me out on this ?

Answer 1

As alain.janinm said

1. modifiedtime instead of modifiedDate

2. = instead of ==

3. I guess you do not need the second to_char because it is already char.

Here is official documentation with Oracle and MySQL examples:

• v5 : FlexibleSearch Tips and Tricks
• v6 : FlexibleSearch Tips and Tricks

Answer 2

There are two issues :

You use == to test equality, using one is enough.

You use p.modifiedDate but the field is called modifiedtime.

The last to_char call is useless.

Unfortunately I can test with an Orale Db but the Flexible search should looks like :

SELECT * from {Product as p} where to_char({p.modifiedTime},'dd/mm/yyyy')='18/04/2017'