dateCompare jql scriptrunner function

[Lakshmi S]

Hi Team,

I am trying to compare two date fields and see if they are more significant than "X" value. I am trying with these two JQLs, but not getting any results, but we have tickets. Are these jqls queries correct or do we need to make any changes ?

project = "TEST" AND (filter = "Team 3" or filter = "Team 4") and status in (Backlog, "In Progress") AND issueFunction in dateCompare(" ", "Prod Accept Stamped Date > Implementation Stamped Date +30d")

project = BIIMPL AND  (filter in ("BIIMPL Team 3") OR filter in ("BIIMPL Team 4")) AND status in (Backlog, "In Progress")  and issueFunction in dateCompare("", "Prod Accept Stamped Date - Implementation Stamped Date > 30" )

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Lakshmi S

To clarify, is JQL the only way you want to use to compare the value of the date fields, or would a REST Endpoint suffice, i.e. to pull the values of the fields from all the issues and display the result in a table or csv?

Please clarify so I can provide a sample.

Than you and Kind regards,
Ram

[Lakshmi S]

Hi @Ram Kumar Aravindakshan _Adaptavist_ 

We can try with REST endpoint also.

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Lakshmi S

For your requirement, you could try something like this:-

import com.adaptavist.hapi.jira.issues.Issues
import groovy.xml.MarkupBuilder

def issues = Issues.search('project = MOCK') // Change the Project Key

def emailBody = new StringWriter()
def html = new MarkupBuilder(emailBody)

html.html {
    head {
        style(type:'text/css', """
            table { border-collapse: collapse; width: 100%; }
            th, td { text-align: left; padding: 8px; }
            tr:nth-child(even){background-color: #f2f2f2}
            th { background-color: #04AA6D; color: white; }""".toString() )
    }
    body  {
        table  {
            thead  {
                tr  {
                    th 'Issue Key'
                    th 'Sample Date'
                    th 'Baseline Date'
                }
                issues.each {
                    def issueKey = it.key
                    def sampleDate = it.getCustomFieldValue('Sample Date') //Change the field name
                    def baselineDate = it.getCustomFieldValue('Baseline Date') //Change the field name
                    tr {
                        td ( issueKey )
                        td ( sampleDate )
                        td ( baselineDate )
                    }
                }
            }
        }
    }
}

emailBody.toString()

Please note that the sample working code above is not 100% exact to your environment. Hence, you must make the required modifications.

Below is the sample output returned:-

Also in your description, you mentioned:-

I am trying to compare two date fields and see if they are more significant than "X" value

How much date range comparison is expected between the two date fields? And also when comparing the date range, do you have any other numeric field to set the difference?

I am looking forward to your feedback.

Thank you and Kind regards,
Ram

[Trudy Claspill]

Hello @Lakshmi S 

Your second example is not valid usage of the dateCompare function. You cannot directly use math functions within the date comparison expression; you can't subtract one date from another to get the difference and then compare that to a value.

https://docs.adaptavist.com/sr4js/latest/features/jql-functions/included-jql-functions/date

Looking at your first example:

project = "TEST" AND (filter = "Team 3" or filter = "Team 4") and status in (Backlog, "In Progress") AND issueFunction in dateCompare(" ", "Prod Accept Stamped Date > Implementation Stamped Date +30d")

Have you verified that the first part returns some issues, without the dateCompare function?

project = "TEST" AND (filter = "Team 3" or filter = "Team 4") and status in (Backlog, "In Progress")

Looking at your dateCompare expression:

"Prod Accept Stamped Date > Implementation Stamped Date +30d"

This would add 30 days to the "Implementation Stamped Date", and then look for issues where the "Prod Accept Stamped Date" is greater than that result. Do you have issues from the results of the first portion of your filter that match that criteria? If so, for confirmation can you tell us what the two date fields contain for such an issue?

[Lakshmi S]

Hi @Trudy Claspill ,

Yes, we have results for this first part.

project = "TEST" AND (filter = "Team 3" or filter = "Team 4") and status in (Backlog, "In Progress")

I want to compare these two fields "Prod Accept Stamped Date"  and "Implementation Stamped Date", and and see if they are greater than x value.

[Trudy Claspill]

The function doesn't support directly evaluating the difference between two dates. It only supports comparing one date to another.

Using the relative operators like +30d you can adjust one of the dates as a workaround.

Looking at your dateCompare expression:

"Prod Accept Stamped Date > Implementation Stamped Date +30d"

This would add 30 days to the "Implementation Stamped Date", and then look for issues where the "Prod Accept Stamped Date" is greater than that result. That would tell you if there was a thirty or more days difference between the dates, with Implementation Stamped Date being the earlier date.

Example:
date1 = 1 November 2024
date2 = 5 December 2024
date1 +30d = 1 December 2024
dateCompare("", "date2 > date1 +30d") TRUE
There is a 30 (or more) difference between date1 and date2, where date2 is later than date1. This statement only works when date2 is a later date than date1.

If you need to be able to evaluate the difference regardless of which date is earlier or which date is later than you would need to use an OR operator and reverse the date fields in the second half:

(issueFunction in dateCompare("", "date2 > date1 +30d") OR issueFunction in dateCompare("", "date1 > date2 +30d") )