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

Learn More

View all

Sign in to view all badges

How to compare exact date without time stamp using query builder

Avatar

Avatar
Validate 50
Level 4
Dinu_Arya
Level 4

Likes

38 likes

Total Posts

181 posts

Correct Reply

16 solutions
Top badges earned
Validate 50
Validate 25
Validate 10
Validate 1
Ignite 5
View profile

Avatar
Validate 50
Level 4
Dinu_Arya
Level 4

Likes

38 likes

Total Posts

181 posts

Correct Reply

16 solutions
Top badges earned
Validate 50
Validate 25
Validate 10
Validate 1
Ignite 5
View profile
Dinu_Arya
Level 4

24-03-2021

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 

AEM date querybuilder

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Boost 100
Level 7
rush_pawan
Level 7

Likes

120 likes

Total Posts

192 posts

Correct Reply

77 solutions
Top badges earned
Boost 100
Validate 1
Ignite 1
Give Back 5
Give Back 3
View profile

Avatar
Boost 100
Level 7
rush_pawan
Level 7

Likes

120 likes

Total Posts

192 posts

Correct Reply

77 solutions
Top badges earned
Boost 100
Validate 1
Ignite 1
Give Back 5
Give Back 3
View profile
rush_pawan
Level 7

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

 

Answers (0)