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.