JQL for status changed by a date defined in custom field

[Matt Melvin]

Hello,

I am trying to establish a JQL statement which looks for tickets moved from one status (Open) to another (Acknowledged) by a certain date.  That date is established within a custom field named "SLA Date".

I have started with the following:

AND status changed from Open To Acknowledged BY "SLA Date".  However, JIRA won't recognize "SLA Date" in this statement

So, if the "SLA Date" is set to 3-24-22, and a ticket was moved from Open to Acknowledged by 3-24-22, I want it to show up in the filter results.

Thanks!

[Ste Wright · Accepted solution]

Hi @Matt Melvin 

This isn't possible to do natively - date operators like BEFORE or ON use specific dates, rather than date fields themselves.

You might be able to find an App on the Marketplace which extends JQL to allow for this - but it's not something I've seen before.

---

I have however got an option for a workaround you could explore, using Scriptrunner:

• Create a custom date picker field to record the date of the transition - eg. TransitionDate
• Populate the field when the transition takes place using Scriptrunner, or a similar Automation App such as Automation for Jira
• Then, use dateCompare in JQL (Scriptrunner JQL function) to confirm if the TransitionDate was before or equal to SLA Date

The JQL might look like...

issueFunction in dateCompare(" ","TransitionDate <= SLA Date")

---

Let us know what you think!

Ste

[Matt Melvin]

Thanks, Stephen.

I find it odd that there is no support of this use case from core JIRA.  I appreciate your feedback.

Matt

[Mark Segall]

Hi @Matt Melvin - Unfortunately, this is not how the Changed operator works.  As you have in your query, Changed is designed to capture the before and after field value. However, By is intended to capture WHO made the change.

Natively, there is no way to query one date field against another (Recommend voting for this enhancement: https://jira.atlassian.com/browse/JRACLOUD-20727).   Until then, you'll need a 3rd party tool such as script runner.

[Matt Melvin]

Thanks, Mark.

I voted for the enhancement and got some laughs from some of the comments within it.

Matt