Custom Date Picker field throws conversion out-of-range value error

[Deleted user]

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

• Using the calendar date picker – whose output format is MM/dd/yy
• Manually entering a date in the MM/dd/yy format

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?

Jim

[Andy Heinzer · Accepted solution]

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

[datavalue,0018-11-15 00:00:00.0]

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

MM/dd/yyyy

and if you change that value, you should also change the javascript picker accordingly to

%m/%d/%Y

Additionally, for the date/time pickers for java and javascript you can change them to

dd/MMM/yyyy h:mm a

and

%d/%b/%Y %l:%M %p

respectfully.

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.  

[Deleted user]

Andrew: Thank You. I'll check into this immediately and let you know what I find.

Jim

[Asha Ganesh]

Hi please can you update this issue 

[Andy Heinzer]

Hi Asha,

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:

1. Are you seeing this particular error in your Jira logs? 
2. Can you tell us what steps you are taking when you see this? (ie issue creation, CSV import, project import, system XML restore process, etc)
3. I'd be curious to see what value you have in the datavalue field, for example: [datavalue,0018-11-15 00:00:00.0] from the example above shows us that the date values in the previous post seemed to only have been collecting 2 digit years, which leads to an ambiguous value when trying to import into a 4 digit year field in SQL on the backend.

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.

Thanks

Andy

[Asha Ganesh]

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

[Andy Heinzer]

Hi Asha,

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

[datavalue,0018-11-15 00:00:00.0]

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:

1. Make a backup of your zip file first.
2. Extract the zip file.
3. open the entities.xml file with a text editor
4. I'm not sure the exact format of this data here, and we might need to see exactly how it look in that file, start by searching for a string like '0019' to see if you can find a unique string that includes all of these 2 digit year values being stored as 4 digit values.
5. You can then do a find and replace to changes these 0019 to values such as 2019.  You would likely need to repeat this for all year values in the data set.
6. Save that entities.xml file
7. Add it back to the zip file along with the active-object.xml file
8. Use this new zip file for the import instead.

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.

Andy