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

Yi Liang November 19, 2024

Hi,

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

How can I write this JIRA SQL

Many Thanks!

3 answers

3 accepted

2 votes
Answer accepted
Florian Bonniec
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 19, 2024

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 November 19, 2024

Hi @Florian Bonniec ,

It is really helpful!

Thanks

1 vote
Answer accepted
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 19, 2024

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 November 19, 2024

Hi @Jack Brickey ,

Thank you for your answer!

I have learnt a lot.

0 votes
Answer accepted
Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 19, 2024

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 November 19, 2024

Thank you so much!

Like Marc - Devoteam likes this
Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 20, 2024

Please accept my answer as a solution.

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

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events