How can I find the JIRAs whose Required Date <= Resolved Date

[Yi Liang]

Hi,

I would like to know the JIRAs whose Required Date <= Resolved Date.

How can I write this JIRA SQL

Many Thanks!

[Florian Bonniec · Accepted solution]

Hi @Yi Liang 

I do not see how to do it with JIRA without app. It should work with Scriptrunner using 

dateCompare

dateCompare(Subquery, date comparison expression)

https://docs.adaptavist.com/sr4jc/latest/features/scriptrunner-enhanced-search/jql-functions

Regards 

[Yi Liang]

Hi @Florian Bonniec ,

It is really helpful!

Thanks

[Jack Brickey · Accepted solution]

Hi @Yi Liang , welcome to the community.

Unfortunately, JQL does not allow you to compare dates. One option you could consider would be to use automation a company with a custom field. The concept is to set the custom field if the resolved date is greater than the required date. Automation allows for this comparison. Then you can use the custom field in your JQL, for example…

date-exceeded = true

[Yi Liang]

Hi @Jack Brickey ,

Thank you for your answer!

I have learnt a lot.

[Marc - Devoteam · Accepted solution]

Hi @Yi Liang 

You could do this with Jira automation

In the automation rule use smart values to do a date compare, in combination with a issue lookup

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-compare--- 

[Yi Liang]

Thank you so much!

[Marc - Devoteam]

Please accept my answer as a solution.

This will help other community member trying to solve the same.