To get all Published page list within specific date

[skumari1]

Hi Team,

I want to list some pages and asset too which has been published in last 20 days.

I am writing below query in query builder but seems something wrong in it.

Can anyone help here

[Jagadeesh_Prakash]

Hi @skumari1 You can modify below query to pages. It should work

• If we want to get the assets modified after a particular date, we can use the query as below:

 SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, "<path of assets to be traversed>") AND p.[jcr:content/jcr:lastModified] >= CAST("<date>" AS DATE)

 For example: SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, "/content/dam") AND p.[jcr:content/jcr:lastModified] >= CAST("2022-04-01T10:36:00.000-05:00" AS DATE)

• If we want to get the assets modified in between a particular date range, we can use the query as below:

 SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, “"<path of assets to be traversed>") AND p.[jcr:content/jcr:lastModified] >= CAST("<From date>" AS DATE) AND p.[jcr:content/jcr:lastModified] <= CAST(“<To date>” AS DATE)

For example: SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, "/content/dam") AND p.[jcr:content/jcr:lastModified] >= CAST("2022-03-01T10:36:00.000-05:00" AS DATE) AND p.[jcr:content/jcr:lastModified] <= CAST("2022-04-20T23:59:59.999Z" AS DATE)

[skumari1]

Hi @Jagadeesh_Prakash 

I have to query last replicated pages/assets so i need to use cq:lastReplicated in place of "jcr:lastModified" in the query?

[skumari1]

Hi @Jagadeesh_Prakash 

When running in query builder its giving the result but while running in sql2

its not giving the result.

[krati_garg · Accepted solution]

For Sites:

path=/content
type=cq:Page
relativedaterange.property=jcr:content/cq:lastReplicated
relativedate.lowerBound=20d

For Assets:

path=/content/dam

type=dam:Asset

relativedaterange.property=jcr:content/cq:lastReplicated
relativedate.lowerBound=20d