Advanced search with a date field

[Thi Phan Đình]

On Jira, I have issues with a custom field named "Actual Start Date".

Now, I want to use Advanced search to query all issues with status changed to closed after the date in the field "Actual Start Date".

I tried to use some queries like:

issuetype not in (Test) and status changed to (Closed) after "Actual Start Date"

issuetype not in (Test) and "Resolved Date & Time" >= "Actual Start Date"

However, it cannot work because: Date value 'Actual Start Date' for predicate 'after' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.

Date value 'Actual Start Date' for field 'Resolved Date & Time' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.

Please kindly help me to find a possible way to query. Or the field "Actual Start Date" can be converted to valid formats ? Thanks for your help.

[Nic Brough -Adaptavist- · Accepted solution]

These JQL functions do not support direct field comparisons.  They can only compare date fields with absolute or relative dates. 

i.e. "<field date 1> is more than 5 days in the past" can be done, but "<field date 1> is after <field date 2>" can not.

[Thi Phan Đình]

Thank you

[Stephen Wright _Elabor8_ · Accepted solution]

Hi @Thi Phan Đình 

Natively, this isn't possible.

You'll need an app which offers more advanced JQL functions to allow you to do a date compare - such as ScriptRunner

You could then check for:

issueFunction in dateCompare("issuetype != Test AND status changed TO Closed", "'Actual Start Date' < resolved")

 ^ This might differ slightly as I don't have that field in our environment.

This JQL searches for:

• All issue types other than Test...
• Which have been transitioned to Closed...
• Where the Resolved Date is after "Actual Start Date"

Ste

[Casey Gould]

It's 2.5 years later, but since date field comparison is a pretty common use case and this question is still popping up in search results, here are some workarounds for those who similarly need to compare date fields in Jira:

As others have said, date field comparison is not possible as a search query within Jira, but you can make date field comparisons in Jira Automation.

Workarounds

For reporting on past issues, there are two options:

1) Query for the other issue parameters, export those results to a spreadsheet, and make the date comparisons in the sheet. It's not going to be nearly as useful, but if you only have a few issues, it's potentially the fastest option.

2) Scheduled automations in Jira allow you to take actions on all issues which both appear in a JQL query AND meet criteria set in the automation (including date field comparisons).

For future issues, you can either keep the scheduled automation in (2) or set similar automations to trigger on changes to your target date fields.

Actions you might want to take in those automations might include:

• add a comment and tag the issue assignee: [~accountid:{{issue.assignee.accountId}}]
• if triggering on a change, add a comment and tag the person who initiated the automation: [~accountid:{{initiator.accountId}}]
• add a label to the issue, which you can then query against

---

Comparing Dates in Jira Automation

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/

To do very simple date field comparisons, you can use the standard "Issue fields condition" to select one date field and use the Field comparison type to select your second date field.

For more complex comparisons, the automation component is called "Advanced compare condition" and you can either compare your two dates with greater than or less than OR (especially if you need "or equal to"), use the smart comparison equals true.

The format to grab a date field called "Field Name" from an issue usually looks like: {{issue.Field Name}}

You'll also want to specify type to avoid comparison wonkiness. For date/time comparisons: {{issue.FieldName.jqlDateTime}}. For date only comparisons, you can use: {{issue.Field Name.jqlDate}}

Good luck!

[Fahad Siddiqui]

@Thi Phan Đình Can you please clarify little more on what report you are trying to generate from system