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

smacdonald2008 wrote...

Here is a blog that i show how to use Dates and JCR SQL2: 

http://scottsdigitalcommunity.blogspot.ca/2015/12/using-date-values-in-jcr-sql2-queries.html

 

 

Thank you.Please correct me if I am wrong, for the solution you provided above, I have to execute this query for each asset.

For example, /content/path1 's [cq:lastReplicated] is 2013-12-20, so path1's expiry date should be 2014-12-20. Because the expiry date is based on [cq:lastReplicated].

Consider another /content/path2 's [cq:lastReplicated] is 2015-12-20, so path2's expiry date should be 2016-12-20.

By using a date range will not work, I have to use a datediff([cq:lastReplicated], NOW)>365 days. Unfortunately, it looks like JCR SQL2 does not have this function.