Find all issues that have the same creation and resolution date via database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
In some cases it can happen that when an issue moves through the stages that depending on how it was created or handled that the resolution date is equal to the creation date.
Environment
Jira
Diagnosis
To find these issue run a query like the following example where the issue contain a certain string in the summarry.
1
2
3
4
5
6
7
8
9
10
11
select id
, issuenum
, project
, reporter
, assignee
, issuetype
, created
, resolutiondate
FROM jiraissue
WHERE SUMMARY like 'MMEA%'
AND CAST(created AS DATE) = CAST(resolutiondate AS DATE);Cause
Can be caused when an issue is resolved on the same day as it was created and then was reopened. Other scenario's could be that a REST request or script that contained this data and needs to be corrected.
Solution
To clear the resolution date, you can use the following query
1
2
3
4
UPDATE jiraissue
SET RESOLUTIONDATE = null
WHERE SUMMARY like 'MMEA%'
AND CAST(created AS DATE) = CAST(resolutiondate AS DATE);Was this helpful?