Compare Dates

[Apryl Harris]

Notes:

1. Jira CLOUD
2. Only have availability to native JQL - No funding to get ScriptRunner or any other add-on

Hello Geniuses in The Community!

Within a group of work items for a given period (say within the prior month), I want to compare Due date to Resolved and the result display only the dates that are a match. Anyone figured out that JQL?

Thank you.

[Trudy Claspill · Accepted solution]

Hello @Apryl Harris 

That is not possible with only native Jira Cloud JQL capabilities. They do not support criteria that compares the value in one field to the value in another field.

If this is something you need to be able to do on an ongoing basis you could consider this alternate solution:

1. Create a new custom field where you can note if the dates are the same.

2. Use an automation rule to review issues where Due date and Resolved are not empty, compare the dates to each other, and set the custom field accordingly. You could either use a scheduled rule, or a rule triggered by changes in either of those fields.

3. In your JQL set your criteria to select issues where that custom field indicates the dates are the same.

[Apryl Harris]

Hello,
HELP! Automation isn't working.

I created a single select field = Date Match where the values are: 

• Dd = Reslvd
• Dd < Reslvd
• Dd > Reslvd
• No Due date (Dd)
  
  

Automation is triggered by a work item status change to a statuscategory = done and is shown below (similar json for each value above).


In advance, thank you!

[Trudy Claspill]

Hello @Apryl Harris 

Please provide

1. Screen images to show your entire rule.

2. screen images showing all the details in the Audit Log from the execution of the rule.

3. Details about the values of the Due date and Resolved fields in the issue that triggered the rule.

4. The actual value in the custom field for that issue when the rule completed.

I can see a few possible problems.

1. You may not have specified correct smart values to get the data from the Due date and Resolved fields. I recommend that you add a Log action after the trigger, and in it add the smart values that you are trying to compare in your If/Else-if conditions. Log actions can be used to print things into the rule execution Audit Log.

You can also find more information about how to reference data in built-in fields using smart values here:

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-issues/

2. The comparison may not be generated the expected result if the field types are different. The native "Due date" is usually date only while Resolved is a date and time. We can learn more about if this is the problem when you provide the audit log output.

[Apryl Harris]

1. Tests performed:

• 1st test - no value in Due date field. I transitioned a work item to done status. I was expecting the value in the Date match field to be "No Due date (Dd)". But nothing.
  • Nothing in the audit logs for this.
• 2nd test - Due date field = 11/18/2025. I transitioned a different to done status. I was expecting the value in the Date match field to be "Dd < Reslvd".
  • Audit log indicates: "No actions performed" (which means this rule executed successfully, however no actions were performed due to a condition stopping the rule execution."

2. I have each if or else-if block set to "Run actions if"...All conditions match. And I tried each if or else-if block set to "Run actions if"...At least one condition matches.

3. Actual values for the custom field named Date Match are in the previous message. But here they are again:

• Dd = Reslvd
• Dd < Reslvd
• Dd > Reslvd
• No Due date (Dd)

Thank you so much!

[Trudy Claspill]

Thank you for that additional information @Apryl Harris 

Can you please tell us what is the Type of condition you added to the trigger? I need that information to create the rule in my own instance to do some testing.

Can you confirm that when you transitioned the item to Done when there was no value in the Due date field, then the rule was not triggered?

Have you tried adding the Log actions as I recommended and doing another test to confirm that the smart values you are using are getting the correct data from the issue?

[Apryl Harris]

1. JQL condition added to trigger

• <statuscategory = done>

2. Yes, I can confirm that on the 1st test, there was no value in the Due date field.

3. I did not try the first time but will try adding the log actions.

Thank you.

[Trudy Claspill]

@Apryl Harris 

Last question for today. Then I'll do some testing and get back to you.

What type of project/space is this rule for? Company Managed or Team Managed? Software, Business, or Service?

Get that information by clicking the ... next to the project/space name in the panel on the left.

[Apryl Harris]

company managed, software.

thank you!

[Artem Taranenko · Accepted solution]

You could pull the data out into something like Google Sheets using Appscript, and do your comparisons and functions there

if this is a recurring report you need and you're trying to avoid costs, this is an option and you can script your data comparison too.

you'd need an api key to call /rest/api/3/search

[Apryl Harris]

Thanks @Trudy Claspill for that recommendation! I'll try it.

I'll reach back out if I have any questions with the automation (I'm not that good at it).

Again, thank you.