Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

sql2 - To get last 30 days assets

Avatar

Level 2

Hi,

 

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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)   

View solution in original post

5 Replies

Avatar

Correct answer by
Community Advisor

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)   

Avatar

Level 2

Hi,

 

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

Avatar

Community Advisor

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.

 

Avatar

Employee Advisor

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.

Avatar

Level 1

Hi, 

 

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