datecompare is not working

[Jean-Christophe Magnin]

Hello,

I am looking for calculation between 2 dates.

First one is ‘Deliver Impacts Assessment Date’ (when the ticket is assigned to someone).

Second one is the first ‘Date of request of approval’ (first, in case there is more than one Date for RFA).

I tried several things (that did not worked) such as:

AND issueFunction in dateCompare("", "Deliver Impacts Assessment Date < Date Approved"))

Or

 AND issueFunction in dateCompare("", "Deliver Impacts Assessment Date - cf[11910] < Date Approved - cf[16554]"))

Do you have any idea to help me please?

Thanks.

Regards,

[said kouzibry · Accepted solution]

This should work

issueFunction in dateCompare("", "'Deliver Impacts Assessment Date' < 'Date Approved'"))

The issue being the missing (single) quotes around the field name

I also notice you are using "AND" which implies you have another part of the query before this,

try incorporating the rest of the query into the first parameter of dateCompare, in case you have a lot of issues in your instance, this would help with the performance.

Another thing I'm curious about is whether you could completely avoid using dateCompare since 'Deliver Impacts Assessment Date' < 'Date Approved' should work natively if both are date fields.(Will not work)

[Jean-Christophe Magnin]

Hi Said and thanks for your answer.

To be more accurate I am looking to compare the 'Deliver Impacts assessment Date' with the first ‘Date of request of approval’ (first, in case there is more than one Date for RFA).

Thus, I've tried: AND "Deliver Impacts Assessment Date" <  "Date Request for Approval"

And it returns: Date value 'Date Request for Approval' for field 'Deliver Impacts Assessment Date' is invalid. Valid formats include: 'YYYY/MM/DD', 'YYYY-MM-DD', or a period format e.g. '-5d', '4aw 2d'.

It looks like it is comparing both dates that do not share the same date format.

Did i missed smthg or...?

[said kouzibry]

No you did not miss anything, I did, it doesn't work because you can't compare two fields, you can only compare a field to a value or the result of a function,

For me your query works as intended 

issueFunction in dateCompare(" ", "Deliver Impacts Assessment Date < Date Approved")

Does it show any errors ?

[Jean-Christophe Magnin]

Hi Said.

issueFunction in dateCompare(" ", "'Deliver Impacts Assessment Date' < 'Date Approved'")

returns: 

Scripted function "dateCompare" compilation failure. Contact administrator and check logs. Message: Field name: 'Deliver Impacts Assessment Date' not found or not a date or datetime.

And

issueFunction in dateCompare(" ", "Deliver Impacts Assessment Date < Date Approved")

returns:

Scripted function "dateCompare" compilation failure. Contact administrator and check logs. Message: Field name: Date Approved not found or not a date or datetime.

By the way, is there a way to get the 1st date the issue is moving to RFA?

[Dan Knapton]

I got the same dateCompare date compilation error.  I was comparing 2 dates that had spaces in the names of the dates.  Per the Scriptrunner documentation I enclosed the date field names with single quotes and got the date compilation error.  When I took the single quotes off it worked.  It seems that the function dislikes single quotes more that it dislikes spaces.

Note also: I tried putting a subquery as the first parameter and it didn't filter.  It didn't fail but the result set was not filtered. Adding additional filter criteria outside the function worked fine.