We’re running JIRA Software Server v7.10.0
I created a Custom Date Picker field whose format is MM/dd/yyyy. Though we had previously used this field without error we are now seeing an error on our Production instance.
Exception occurred: com.altassian.jira.exception. DataAccessException: org.ofbiz.core.entity. GenericEntityException: while inserting: [GenericEntity:CustomFieldValue] [parentkey,null][customfield,10300][issue,20703][datavalue,0018-11-15 00:00:00.0][id,417402][updated,1542374992188] (SQL Exception while executing the following: INSERT INTO dbo.customfieldvalue (ID, ISSUE, CUSTOMFIELD,UPDATED,PARENTKEY,STRINGVALUE,NUMBERVALUE,TEXTVALUE,DATEVALUE,VALUETYPE) VALUES (?,?,?,?,?,?,?,?,?,?) (The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.))
When I tested Production by
we get the error.
If I do the exact same test in Development, MM/dd/yy is converted to MM/dd/yyyy (which is what I expected to happen) and no error is thrown.
Does anyone have any suggestions on how I might resolve this?
The error you are seeing is specifically a MS SQL exception. Jira can be used with a number of different database types, and each of these can have slightly different data types for storing this info. In this case, the date value Jira is trying to store to SQL is
Which is actually not value I think you want to try to store here. I'm guessing you really want this date to show a 2018 year and not a 0018 for the year, yes?
In this case, what I would recommend would be to take a closer look at what values you have set for the date and date/time pickers in the advanced settings of your Jira administration. There is also a KB about this that I think can help in Changing the Due Date Input Format.
In this case, I think you can avoid this problem by changing your
jira.date.picker.java.format to a value of
dd/MMM/yyyy h:mm a
%d/%b/%Y %l:%M %p
This way, when you pick a date from the calendar field in Jira, it will actually supply a full 4 digit year to be stored in SQL.
I'm not sure what kind of update you are looking for here. The error presented above was clearly a SQL error. I believe that the steps I gave above could be useful in at least preventing this problem as well as potentially provide a means to import such data in a format that works with that datatype in SQL.
To better serve you here, I have a few questions for you:
Perhaps you can share with us your specific stacktrace output here so we can better understand the problem you are seeing here and perhaps we can offer some more specific steps you can try to address this problem.
Hi Andy ,
I am facing similar log during import . I checked with date.picker.java.format which are same in source and target instance
and I have many issues already by changing the format I am not sure whether it will affect the available tickets
(SQL Exception while executing the following:INSERT INTO JiraSchema.jiraissue (ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
) (The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
I think I understand now what causes this. By default Jira Server will default to only use a two digit year for dates. Since Jira can be connected to different database types (MS SQL, MySQL, Postgresql, and Oracle), each of these have slightly differing datatype for storing such dates. Some will presume to know the meaning of a 2 digit year, while others will not.
I can tell you're likely using a Microsoft SQL database on the backend of the system you're doing the import to. This is because the SQL exception here is specific to that database type. The datetime datatype has a limitation of valid years, specifically valid values include January 1, 1753, through December 31, 9999.
But since the datetime2 datatype has a valid range of January 1,1 CE through December 31, 9999 CE, it doesn't understand how to convert this value.
I'm betting that your date values look something like this
Where you likely expect the year to be 2018, but this data was only ever stored as just '18' for the year. Since you didn't include the entire stacktrace, I'm taking a guess about the values you have here.
As for what to do to correct this, well, that might be a complex set of steps to address this. I would probably try to extract the backup file, and look to edit any/all of these 00XX years and replace them with 20XX as this is most likely the correct values.
What kind of import is this? Project import (where you're using an XML backup zip file), or is this a CSV/JSON import into Jira? If this is a project import that uses the XML backup, I'd recommend that you try these steps:
This should let you get past that problem and import that data provided that we corrected the instances of that 2 digit year.
Let me know the results.
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events