nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JQL and start/end dates

Dennis_H

Is there a way to run a JQL statement that will display all issues in a project with 2 date fields that do not have the same date? We have a custom start and end date field and quite a few of them have the same date (for issues that haven't been dealt with yet). I'd like to run a search that doesn't include those issues.

When I try to create one and choose the field (Est Start Date) then choose != all I can choose are date options like current date, before today, etc. I'd like to be able to use something that lists issue where Est Start Date and Est End Date are not equal.

Accepted answers

All comments

Jack Brickey

Unfortunately that option does not exist without an addon.

Bill Sheboy

Hi @Dennis_H

Yes, and to what Jack noted... How often do you need to perform this query/check?

one time: export to a spreadsheet and check with a formula
lots of times: consider purchasing a JQL add-on from the marketplace
somewhere between one and lots: add a custom field for the days difference between the dates, create an automation rule to set the value, and then query on the custom field. Or if you don't want to add the new field and only expect a few "hits", write any discrepancies to the audit log.

Best regards,

Bill

John Funk

Hi Dennish,

You can't do it with JQL - it does not have the functionality to compare dates. But you can do it by creating an Automation For Jira rule based on a scheduled trigger.

Just write the JQL to get all of the issues you want to check, for instance Project = ABC

Then use the Advanced Compare Condition to compare the two date fields.

Finally, add a new Action to send an Email and include lookupIssues in the body.

Here is some more information to get started with Automation For Jira if you are not familiar with it:

There are two types of automation:

Global: In the Free version, you get 100 executions per product, per month
Project: In all versions, you get unlimited executions per month

Automation Basics: https://www.atlassian.com/software/jira/guides/expand-jira/automation

You can do this via global rules to apply to all projects also. You'll need to be a Jira Admin/Site Admin, go to Jira Settings > System > Automation Rules (left-hand menu).

For more on using Automation For Jira see these help pages.

Jira Automation Template Library to help get you started quickly:

https://www.atlassian.com/software/jira/automation-template-library#/labels/all/customLabelId/1453

Bill Sheboy

Hi @John Funk

For a scheduled trigger, I think that would end up sending an email for each issue found. One challenge is the custom date fields aren't available with LookupIssues, so that cannot collapse to one email. That is why I was thinking of the custom field to store the date difference and treat reporting/analysis as a separate issue.

Please help me understand if I am missing something as that would be a great solution otherwise. Thanks!

Best regards,

Bill

John Funk

Hey Bill - but if you are just needing a list of issue that meet the Condition, you don't need to put the date in the lookupIssues section - just the key and maybe summary.

Would that work?

Bill Sheboy

Hi, John. I guess I'm not following what you suggested, so "maybe" ;^)

I thought you were suggesting this:

Trigger: scheduled on JQL
Advanced compare condition: for the date check
Action: lookup issues ???
Action: send the email using lookup

I was noting that after the scheduled trigger, everything else in the rule gets run for each issue returned by the trigger's JQL. So, won't steps 2-4 repeat?

And, if the compare could be done with JQL alone (including the date check), that could be used stand-alone, or in the lookup issues with no scheduled trigger. Right?

Thanks!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-software

jira-software-cloud