How to change date custom field format from date without time to a date with time in Jira via the 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
The purpose of this article is to show how a user can change the date-time format of an existing date Custom Field.
⚠️ Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Environment
8.x.x
Solution
1- Stop Jira
2- Back up your database
3- Find the date customield ID you would like to modify:
How to find id for custom field(s)?
4- Confirm with query below the CUSTOMFIELDTYPEKEY and CUSTOMFIELDSEARCHERKEY
For example for the customefiled id = 10500
1
Select * from customfield where customfieldtypekey like '%com.atlassian.jira.plugin.system.customfieldtypes:datepicker%' and customfieldsearcherkey like '%com.atlassian.jira.plugin.system.customfieldtypes:daterange%';5- Modify the date customefield with the id (found in step 3) from Date field without time to a date field with time
1
2
UPDATE customfield SET CUSTOMFIELDTYPEKEY='com.atlassian.jira.plugin.system.customfieldtypes:datetime', CUSTOMFIELDSEARCHERKEY ='com.atlassian.jira.plugin.system.customfieldtypes:datetimerange' where ID='10500';
commit;6- Start Jira
7- Result
The date custom field before the change
The date custom field after the update
Was this helpful?