Search code examples
javasap-commerce-cloud

Date subtraction in FlexibleSerchQuery

I am trying to write a FlexibleSearchQuery to find records that are older than 30 days in hybris connected to a hsqldb database but the below code throws a 

org.hsqldb.HsqlException: incompatible data types in combination

Query:

final FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery("
SELECT * FROM CUSTOMER AS c WHERE c.timestamp < TRUNC(SYSDATE) - 30")

Could someone please advice on how to fix this issue ?

Solution

  • +1 to HybrisHelp answer. You should calculate the date and pass it on to flexible query.

    A shorter version of same using Java 8 time api. 

    final FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery("
SELECT * FROM CUSTOMER AS c WHERE c.timestamp < ?inputDate")

flexibleSearchQuery.addQueryParameter("inputDate", 
                              new DateTime(Instant.now()).minusDays(30).toDate());