How to compare exact date without time stamp using query builder | Community
Skip to main content
Dinu_Arya
Dinu_Arya
Level 6
March 24, 2021
Solved

How to compare exact date without time stamp using query builder

  • March 24, 2021
  • 1 reply
  • 2503 views

Hi Team,

 

I have to get the nodes using the date value YYYY-MM-DD but the node also contains timestamp.

Kindly let me know how can I do that?

I tried below but got the exception-

map.put("daterange.property", "joiningDate");
map.put("daterange.lowerBound", "2021-02-16" + "-01-01");
map.put("daterange.lowerOperation", ">=");
map.put("daterange.upperBound", "2021-02-16" + "-12-31");
map.put("daterange.upperOperation", "<=");

 

Exception:

javax.jcr.query.InvalidQueryException: java.text.ParseException: Query:
/jcr:root/content/myapp//*[((@joiningDate>= and @(*)joiningDate<= ) and jcr:like(@sling:resourceType, 'myapp/components/myComp'))]; expected: )

 

Caused by: java.text.ParseException: Query:
/jcr:root/content/myapp//*[((@joiningDate>= and @(*)joiningDate<= ) and jcr:like(@sling:resourceType, 'myapp/components/myComp'))]; expected: )

@2439127 @edubey 

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Pawan-Gupta

    Hello,

     

    It seems your query is not built correctly because of that the xpath query which system generated from your query builder query is not correct and failing.

     

    when i did below as an example

     

    path=/content/mysite
    type=nt:base
    daterange.property=joiningDate
    daterange.lowerBound=2021-03-01T09:58:00.000-08:00
    daterange.lowerOperation=>=
    daterange.upperBound=2021-03-24T09:58:00.000-07:00
    daterange.upperOperation=<=

     

    and ran it as query builder through 

    /libs/cq/search/content/querydebug.html

     

    it works fine and give correct XPATH query as response. you can use it to debug your self.

     

    Also, i was wondering why not to use directly SQL2 query if you dont have limitation to use because query builder will eventually run as SQL2 query. so your above query in SQL2  looks as an example (based on your need)

     

    select * from [cq:Page] as s WHERE ISDESCENDANTNODE([/content/mysite]) and s.[node/joiningDate] >= CAST('2021-03-01T09:58:00.000-08:00' as DATE) and s.[node/joiningDate] <= CAST('2021-03-24T09:58:00.000-07:00' as DATE)

     

    OR

     

    select * from [cq:Page] as s WHERE ISCHILDNODE([/content/mysite]) and s.[node/joiningDate] >= CAST('2021-03-01T09:58:00.000-08:00' as DATE) and s.[node/joiningDate] <= CAST('2021-03-24T09:58:00.000-07:00' as DATE)

     

    hope it helps!!

     

    1 reply

    Pawan-Gupta
    Pawan-GuptaAccepted solution
    Level 8
    March 24, 2021

    Hello,

     

    It seems your query is not built correctly because of that the xpath query which system generated from your query builder query is not correct and failing.

     

    when i did below as an example

     

    path=/content/mysite
    type=nt:base
    daterange.property=joiningDate
    daterange.lowerBound=2021-03-01T09:58:00.000-08:00
    daterange.lowerOperation=>=
    daterange.upperBound=2021-03-24T09:58:00.000-07:00
    daterange.upperOperation=<=

     

    and ran it as query builder through 

    /libs/cq/search/content/querydebug.html

     

    it works fine and give correct XPATH query as response. you can use it to debug your self.

     

    Also, i was wondering why not to use directly SQL2 query if you dont have limitation to use because query builder will eventually run as SQL2 query. so your above query in SQL2  looks as an example (based on your need)

     

    select * from [cq:Page] as s WHERE ISDESCENDANTNODE([/content/mysite]) and s.[node/joiningDate] >= CAST('2021-03-01T09:58:00.000-08:00' as DATE) and s.[node/joiningDate] <= CAST('2021-03-24T09:58:00.000-07:00' as DATE)

     

    OR

     

    select * from [cq:Page] as s WHERE ISCHILDNODE([/content/mysite]) and s.[node/joiningDate] >= CAST('2021-03-01T09:58:00.000-08:00' as DATE) and s.[node/joiningDate] <= CAST('2021-03-24T09:58:00.000-07:00' as DATE)

     

    hope it helps!!

     

      Terms & ConditionsAccessibility statement

      Loading footer...