Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

How to find all the assets that has been published for more than 1 year using JCR SQL2?

Avatar

Level 3

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

1 Accepted Solution

Avatar

Correct answer by
Level 10
4 Replies

Avatar

Correct answer by
Level 10

Avatar

Level 3

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.

Avatar

Level 10

Why won;t a date range work - set the first date to a longer time period and then the recent date to a year ago. For testing - see if that works, I have used a date range in many queries.