sql2 - To get last 30 days assets

Forum|Forum|4 years ago
January 18, 2022
2 replies
2311 views

Hi,

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by lukasz-m

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)

lukasz-m

Accepted solution

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)

J

james_aemAuthor

Level 2

Hi,

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

lukasz-m

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.