SQL2 Query to find out DAM Asset Expiry Date | Community
Skip to main content
Himanshu_Singhal
Community Advisor
Himanshu_SinghalCommunity Advisor
Community Advisor
June 21, 2016
Solved

SQL2 Query to find out DAM Asset Expiry Date

  • June 21, 2016
  • 4 replies
  • 3081 views

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

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 Sunil_Chowdhary

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.

4 replies

smacdonald2008
smacdonald2008
Level 10
June 21, 2016

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
Jitendra_S_Toma
Level 10
June 21, 2016

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
Sunil_ChowdharyAccepted solution
Level 2
June 24, 2016

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
Community Advisor
Himanshu_SinghalCommunity AdvisorAuthor
Community Advisor
June 25, 2016

Thanks for your replies.

I wrote the same query :)

 

Regards,

Himanhsu

Terms & ConditionsAccessibility statement

Loading footer...