1. In project TOP you have an issue type Dev Task which has two date-picker fields, My Due Date and Completed On.
2. You want to find all issues where the completion date is greater than the due date.
JQL does not let you compare two date fields, but ScriptRunner add-on does.
As per ScriptRunner documentation, you use the dateCompare() function, like this:
project = TOP and issuetype = "Dev Task" and "Completed On" is not EMPTY and "My Due Date" is not EMPTY and issueFunction in dateCompare("", "Completed On > My Due Date)")
This query works fine when both fields are of type date-time picker.
However, if either or both fields are of type date picker, the above query sometimes does not work correctly - when the two fields have the same date - you might get them in the query
The underlying reason is that Jira keeps all dates in Unix Epoch format which includes the time (hours/minutes/seconds).
In the case of date picker fields there is no way to tell what the time would be.
The dateCompare() function retrieves the internal values of the two fields and compares them numerically.
For example, if the internal value of Completed On is 2019-03-13 17:00:15 and value of My Due Date is 2019-03-13 17:00:05, the issue will be returned in the query even though it should not, since both fields are the same date.
The workaround proposed by ScriptRunner support is to use Groovy code in the JQL which will clear the time part of the fields. Note that in this case you cannot use the field names and you must use the custom field IDs:
project = top AND cf is not EMPTY AND cf is not EMPTY AND issueFunction in expression("", "customfield_18388.clearTime() > customfield_18384.clearTime()")
The cf and cf are the internal JIRA names of the custom fields My Due Date and Completed On, respectively.
I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events