Adobe Experience Manager Sites & More

Rui_J · 10/25/16

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

smacdonald2008 · 10/25/16

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

View solution in original post

smacdonald2008 · 10/25/16

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

Rui_J · 10/25/16

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.

smacdonald2008 · 10/25/16

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.