How do I use a Date Picker field in a JQL query to find updates made after that date?

Marcos Michel October 8, 2024

We have a custom field in the issues called Sprint Start Date which through an automation gets updated with the start date of the sprint for all issues in that sprint or added after the sprint started.

I want to see which issues have not transitioned during that said sprint but we don't have access to startOfSprint() function.

I thought I could use:
NOT status changed after "Sprint Start Date"
or
issuefunction in dateCompare("", "'Sprint Start Date' > updated")

 

But none of these work.

2 answers

0 votes
Jim Knepley - ReleaseTEAM
Atlassian Partner
October 8, 2024

I don't have a good answer, but an observation:

The documentation for dateCompare has examples, the Subquery parameter isn't empty, and notes:

Almost all of the functions require a subquery as a first parameter. If you provide an empty string (e.g. ""), your query will be inefficient because it matches all issues in your Jira instance.

That blank parameter may not be the problem, but it's certainly a problem.

Marcos Michel October 8, 2024

It doesn't take the custom field and I don't know why

0 votes
Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 8, 2024

Hi @Marcos Michel 

Welcome to the community.

Jira has no compare options.

The only JQL I can think of is:

status not changed after "date" and sprint = "sprint id".

Otherwise you would need to have a marketplace app like;

  • JXL - Sheets Hierarchy Structure Sum-up Time in status Table
  • Time in Status

If you really want to compare you would need an app to export data to a BI tool and create a report there.

Marcos Michel October 8, 2024

it does. You can use issuefunction in dateCompare compares dates within a subquery. But it doesn't work with my custom field and I don't know why.

This is the documentation to this

Date (adaptavist.com)

Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 8, 2024

Hi @Marcos Michel 

Ah, you have scriptrunner installed on the instance, that gives extra functions, above the default JQL

you could use: issuefunction in dateCompare("",  "updated > Sprint Start Date")

As @Jim Knepley - ReleaseTEAM mentions a subquery is useful as performance can be an issue. You could at something like; project = <project_key> and sprint in openSprints()

Marcos Michel October 8, 2024

This was actually very useful. I was using 'Sprint Start Date' with the single quote thinking I had to because of the multiple words. 

I'm still struggling to get what I need though.

I'm trying to get all the issues that have not transitioned after the sprint started.

I thought the following was going to do it, but it didn't. 

issuefunction in dateCompare("not status changed", "updated >= Sprint Start Date")

 

Suggest an answer

Log in or Sign up to answer