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 for startofmonth / endofmonth returning different work items compared with actual dates

Siobhan.Claire.Lear

I have a filter that I'm trying to update so that it can be used month on month without the need to manually update the dates for start of the month and end of the month.

The aim of the filter is to display client tickets that were tagged with a service level that were created and closed in the previous calendar month.

A search led me to believe I can use "startOfMonth(-1)" and "endOfMonth(-1)" but when I compare the results of this filter with the previous version which used the actual dates i.e. 2025-05-01 & 2025-05-31, I get 1 additional work item. When I look at this work item, it should not be included in the filter.

Original filter query:

project not in ("PROJECT 1", PROJECT 2, "PROJECT 3", "Internal QA") and statusCategory in (Done) AND "Service Level" in ("Level 1", "Level 2", "Level 3", "Level 4") AND resolved >= 2025-05-01 AND created <= 2025-05-31

Results = 86

Updated filter query:

Results = 87

The 1 additional work item was created on 31 May 2025 & resolved on 3 June 2025 so should not be included in this filter.

Is this a bug or am I misunderstanding the use of the startofmonth / endofmonth option?

Is there a better way to do this?

Thanks!

Accepted answers

Duc Thang TRAN

Hello,

I think the difference is due to created <= 2025-05-31, which only includes issues created up to midnight on May 31 (i.e., 2025-05-31 00:00).

You can try using: created <= "2025-05-31 23:59" to include the entire day.

In contrast, endOfMonth(-1) refers to the end of the day on May 31 (2025-05-31 23:59:59.999).

Regarding your question about additional work: yes, it is included in your JQL, because the ticket was created on May 31 and resolved in June. Since resolved >= 2025-05-01, it still meets the condition

Hope this can help

All comments

Duc Thang TRAN

Hello,

I think the difference is due to created <= 2025-05-31, which only includes issues created up to midnight on May 31 (i.e., 2025-05-31 00:00).

You can try using: created <= "2025-05-31 23:59" to include the entire day.

In contrast, endOfMonth(-1) refers to the end of the day on May 31 (2025-05-31 23:59:59.999).

Hope this can help

Gor Greyan

Hi @Siobhan.Claire.Lear

Welcome to the Atlassian Community!

You are not facing a bug - this behaviour is due to how your query logic works.

To capture ticket that are both created and resolved in May 2025, you need the following JQL.

resolved >= startOfMonth(-1) AND resolved <= endOfMonth(-1)
AND created >= startOfMonth(-1) AND created <= endOfMonth(-1)

For your example, also adding the Service Levels.

project NOT IN ("PROJECT 1", PROJECT 2, "PROJECT 3", "Internal QA")
AND statusCategory = Done
AND "Service Level" IN ("Level 1", "Level 2", "Level 3", "Level 4")
AND resolved >= startOfMonth(-1) AND resolved <= endOfMonth(-1)
AND created >= startOfMonth(-1) AND created <= endOfMonth(-1)

If your goal is to automate this month-over-month, using startOfMonth(-1) and endOfMonth(-1) combo as shown above is the right approach as long as you apply both created and resolved fields.

Let me know if you have any questions.

Siobhan.Claire.Lear

Thank you!! This explains the discrepancy as when I add that with the dates, I get the same results.

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-cloud