Need JQL Query to Filter Issues Between Last Month’s 24th and This Month’s 24th

[Doğancan Uğurlu]

Hello,

I need a JQL query that dynamically filters issues created after the 24th of the previous month and before or on the 24th of the current month. The query should automatically update each month without requiring manual date changes.

I have tried using expressions like:

project = ABCD
AND created >= startOfMonth(-1) + 24d
AND created <= startOfMonth() + 24d

However, Jira does not support direct date calculations using + 24d, and I receive an error about + being a reserved character.

I also attempted to use formatDate() to generate the correct date dynamically, but JQL does not support this function either.

Could you please provide a fully dynamic JQL query that meets the following criteria?

1. Fetch issues created after the 24th of the previous month
2. Include issues created before or on the 24th of the current month
3. Automatically update every month without manual date entry

Thank you in advance for your help!

Best regards,

[Bill Sheboy]

Hi @Doğancan Uğurlu -- Welcome to the Atlassian Community!

Jira Query Language (JQL) is not a SQL, and thus does not have features as you describe.  It is a purpose-built language to search for issues within the confines of Jira features.

The workarounds for such features depend upon how frequently you need it and willingness to invest money or time:

• Need this one time
  • Export the issues to a spreadsheet and perform the comparisons with spreadsheet functions
• Need this often
  • Have money to invest
    • Check the Atlassian Marketplace for addons to JQL features to do this
    • Check the Atlassian Marketplace for reporting addons / dashboard gadgets to do this
  • Have time to invest
    • Create a saved filter and use an automation rule to updates it on the first of the month using the Jira REST API endpoint and the Send Web Request action.  This approach require learnings about automation rules and experimentation to confirm you build it correctly.

Kind regards,
Bill

[Walter Buggenhout]

Hi @Doğancan Uğurlu and welcome to the Community!

Adding to both very valid answers from both @Bill Sheboy and @Mark Higgins, you can come pretty close to what you were initially trying to do with the following JQL:

created >= startOfMonth(-7d) AND created <= endOfMonth(-7d)

As @Mark Higgins rightfully mentioned, this will not cover every month dynamically as not every month has the same number of days. And so you will need a couple of alternatives to cover for every month. But I wanted to share this one as it actually is a working JQL alternative on your initial attempt, just with a slightly tweaked syntax to do calculations.

Hope this helps!

[Mark Higgins]

Hi @Doğancan Uğurlu 

Welcome to the community.

As Bill has said, nope, JQL, will not do that, and so you will need a work around.

If it is something that you are running once a month, then you could create need several filters, based on, the number of days, in the month i.e. 28,29,30,31.

The best time to run one of these filters would be the 24th.

As an example, I've run this 

created >= -30d order by created asc 

today, on the 16th March, and so its returned everything from the 14th Feb, because this year Feb has 28 days.

So, if I change that to -28d then I will get the last 28 days.

If you run that on March 24th, created >= -28d you will get all tickets created from Feb 24th.

Hence, you need 4 subscription filters for each month, and the one you run depends on the number of days in the previous month.

Filter 1 - created >= -30d  , October, May,July,December

Filter 2 - created >= -31d , February, April, June, August,September, November,January.

Filter 3 - created >= -28d , March

Filter 4 - Created >= -29d Run in March on a leap year.

You could create automation rules to run this and send to the people who need it as well.

If you need to run these filters on the 1st, then you are going to need to adjust accordingly, such as:

created >= -37d and created <= -7d ( for months that have 30 days)

Not pretty but ...

Hope that helps

Mark