How to write a JQL query to compare a JPD date?

Glenn A

Hi!

OK, so, I have issues in JPD with the project due date set. This 'date' is actually a string of a JSON object with a start and end date. The following allows me to consistently extract the actual date well enough for use in automations:

issue.customfield_12345.substring(29,39)

Now, I want to create a JQL query to give me the set of issues with a due date this coming month. JQL doesn't seem to like that string enough to use it in any number of typical JQL date or comparison functions. I've tried converting from string to date too... maybe incorrectly?

Any thought on the JQL that would do this... conceptually along the lines of:

project IN ("Project") AND '{{issue.customfield_12345.substring(29,39)}}' < endOfMonth()

Actually, rather than overthinking it, how can I write a JQL query to compare a JPD date field to a date (like today)?

Thoughts?

Thanks!

Eric Sibomana

Hello Glenn,

Regarding your question about comparing a JPD date field to a date using JQL, I understand the complexity of your situation. Unfortunately, JQL doesn't support direct string manipulation or JSON parsing within queries, making comparing dates stored in JSON format challenging.

However, I can suggest a few potential workarounds:

1. Custom Field Optimizer: Consider using a Custom Field Optimizer app to create a new custom field that extracts and stores the data in a proper date format. This would allow you to use standard JQL date comparisons.
2. Scripted JQL Function: If you have ScriptRunner for Jira installed, you could create a custom scripted JQL function to parse the JSON and extract the date for comparison.
3. Automation Rule: Create an automation rule that extracts the date from the JSON and updates a separate date custom field. You can then use this field in your JQL queries.
4. Search Extension: Implement a search extension that allows for custom field parsing and comparison logic.

For your specific use case of finding issues due in the coming month, once you have the date in a proper format, you could use a JQL query like this:

project IN ("Project") AND customfield_DATE >= startOfMonth() AND customfield_DATE <= endOfMonth()

Where customfield_DATE is your extracted and properly formatted date field. 

I recommend exploring the Custom Field Optimizer or automation rule options first, as they're generally easier to implement and maintain.

Don't hesitate to reach out if you need further clarification or assistance!

Best regards,
Eric