SQL2 Query to find out DAM Asset Expiry Date

[Himanshu_Singhal]

Hi,

I need to find out list of all dam assets that is going to be expired before 7 days of their expiry date,

Could you please provide me suggestion on it?

Thanks,

Himanshu

[smacdonald2008]

You need to do a date based SQL2 search:

http://scottsdigitalcommunity.blogspot.ca/2015/12/using-date-values-in-jcr-sql2-queries.html

Hope this helps... 

[Jitendra_S_Toma]

How are you calculating the expiry date? And how would you know that particular assets would expire in 7 days?. if you know this then use the date range in SQL query (Just google it).

[Sunil_Chowdhary · Accepted solution]

Hi @Himanshu,

I hope you already have got solution.

But just for others :-

Assuming prism:expirationDate as expiry date, if offTime as expiry date replace accordingly.

SQL2 query :  SELECT p.* FROM [nt:base] AS p WHERE p.[prism:expirationDate] >= CAST('2016-01-01T00:00:00.000Z' AS DATE) AND p.[prism:expirationDate] <= CAST('2016-01-07T23:59:59.999Z' AS DATE)

(Note the date might need to  be formatted using Java API to make this query dynamic)

Reference :-http://labs.6dglobal.com/blog/2014-10-07/9-jcr-sql-2-queries-every-aem-dev-should-know/

Hope it helps. Take care.

[Himanshu_Singhal]

Thanks for your replies.

I wrote the same query 🙂

Regards,

Himanhsu