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

Question

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 · Accepted Answer

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

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded