How to compare exact date without time stamp using query builder

[Dinu_Arya]

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: )

@bslokesh @edubey 

[Pawan-Gupta · Accepted solution]

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!!