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 find all the assets that has been published for more than 1 year using JCR SQL2?

Avatar

Avatar
Validate 10
Level 2
Rui_J
Level 2

Likes

4 likes

Total Posts

39 posts

Correct Reply

1 solution
Top badges earned
Validate 10
Validate 1
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 10
Level 2
Rui_J
Level 2

Likes

4 likes

Total Posts

39 posts

Correct Reply

1 solution
Top badges earned
Validate 10
Validate 1
Boost 3
Boost 1
Affirm 1
View profile
Rui_J
Level 2

25-10-2016

How do I find all the assets that has been published for more than 1 year using JCR SQL2 ? Because each asset has a different cq:lastReplicated value, I believe I need to do something like 

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/content/myapp') AND parent.[jcr:primaryType]='cq:Page' AND datediff(child.[cq:lastReplicated], CURRENTDATE)>365

For CURRENTDATE I can get it in java and cast it in SQL2, but how do I do a datediff, that part I don't know.

Please help.

Thank you

View Entire Topic

Avatar

Avatar
Validate 10
Level 6
leeasling
Level 6

Likes

73 likes

Total Posts

292 posts

Correct Reply

75 solutions
Top badges earned
Validate 10
Validate 1
Boost 50
Boost 5
Boost 3
View profile

Avatar
Validate 10
Level 6
leeasling
Level 6

Likes

73 likes

Total Posts

292 posts

Correct Reply

75 solutions
Top badges earned
Validate 10
Validate 1
Boost 50
Boost 5
Boost 3
View profile
leeasling
Level 6

25-10-2016

Can't help with SQL2, but, using the query building/debugger it's pretty darn simple.

http://localhost:4502/libs/cq/search/content/querydebug.html?_charset_=UTF-8&query=type%3Dcq%3APage%...