sql2 - To get last 30 days assets

[james_aem]

Hi,

I wanna get the assets(i.e. using sql2) which is recently uploaded on dam.

[lukasz-m · Accepted solution]

Hi @james_aem, I think you can try SQL2 query like below - of course you will need to set approprite date range depending on your needs/requirements.

SELECT * FROM [dam:Asset] AS asset WHERE ISDESCENDANTNODE([/content/dam]) AND asset.[jcr:created] >= CAST('2021-12-18T00:00:00.000Z' AS DATE) AND asset.[jcr:created] <= CAST('2022-01-18T00:00:00.000Z' AS DATE)   

[james_aem]

Hi,

our date format saved as mm/dd/yyyy on jcr:content (custom property which we imported from third party system).

[lukasz-m]

Custom property is not a problem, you can modify above query changing property like below:

asset.[jcr:created]

// change to

asset.[jcr:content/yourCustomProp]

There could be a need to extend damAssetLucene index to include your custom property.

Regarding date format, it seems you are storing your date as a string, and this will be problematic, you will probably need some additional java code to filter your results. I would recommenced to convert date into ISO 8601-based compliant format, before you save it in repository and make sure your date property is stored as Date type. This will allow you to use query by date using SQL syntax only.

[Nirmal_Jose]

If you want to get the list of assets, just use the query in query runner and get the results.

if you want to package the assets out, you can use https://adobe-consulting-services.github.io/acs-aem-commons/features/packagers/query-packager/index.... and use the query, which can get you a content package with the assets.

[VishwanathE1]

Hi, 

I wanna get the assets(i.e. using sql2) which is recently downloaded from dam.