Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to compare exact date without time stamp using query builder

Dinu_Arya
Level 7
Level 7

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 

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution
Pawan_Gupta_
Correct answer by
Community Advisor
Community Advisor

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

 

View solution in original post

1 Reply
Pawan_Gupta_
Correct answer by
Community Advisor
Community Advisor

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

 

View solution in original post