glidefunction:datediff doesn't work if first parameter is newer than second

[Shawn McCormick]

I am trying to report on the average difference between a Task Closed date (closed_at) and the Task Due date (due_date).  I have configured a function field to calculate the difference.

glidefunction:datediff(due_date,closed_at)

The screenshot below shows examples of values for the function field u_rpt_due_date_delta (please disregard the red background). 

For records where the Closed date is before the Due date, the calculation appears correct (records 1,2,4)

But if the Closed date is after the Due date (records 3,6) the calculation is incorrect.  It's as if the value is missing the Days component.

Am I not configuring this correctly?  Or does datediff require that the first parameter is later than the second parameter?

[Allen Andreas · Accepted solution]

Hi @Shawn McCormick 

The first parameter should be the latest date/time.

I believe it equates to: date1 - date2...so the first parameter that's farther out should go first to be subtracted by the other value.

So unfortunately, unless it's always going to be what you want for that first parameter, you can't do any JavaScript, etc. in a function field. Otherwise, you'd want to consider using a business rule or something and doing the calculation for you where you can evaluate one field being larger than the other for an appropriate parm1, etc.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Hitoshi Ozawa]

Hi Shawn,

It's a limitation with GlideDuration.

I've tested using script to get the GlideDBFunctionBuilder datediff() and gs.dateDiff() and both works fine. However, GlideDateTime substract does not.

I've created a table with fields u_due_date and u_closed_at. Created a record with u_closed_at = 2022-06-20 12:19:07 and u_due_date = 2022-06-09 00:00:00

var functionBuilder = new GlideDBFunctionBuilder();
var dateDiffFunction = functionBuilder.datediff();
dateDiffFunction = functionBuilder.field('u_due_date');
dateDiffFunction = functionBuilder.field('u_closed_at');
dateDiffFunction = functionBuilder.build();

var gr = new GlideRecord('<table with u_due_date and u_closed_at>');
gr.addFunction(dateDiffFunction );
gr.addQuery('sys_id', '<sys_id of record to test>');
gr.query();

if (gr.next()) {
  var closedAt = gr.u_closed_at;
  var dueDate = gr.u_due_date;
  var diff = gr.getElement(dateDiffFunction);

  var diffSeconds2 = gs.dateDiff(closedAt, dueDate, false);
  gs.print(diffSeconds2);

  gs.print('diff function result:' + diff.getDisplayValue());
}

Results are as follows. gs.dateDiff returned number of days. datediff() functions returned difference between 1970/01/01 00:00:00. That is, in both cases, the script returned a correct value.

*** Script: -11 11:40:53
*** Script: diff function result:1969-12-20 11:40:53

GlideDateTime substract() returns result in GlideDuration. 

var gdt1 = new GlideDateTime("2022-06-20 12:19:07");
var gdt2 = new GlideDateTime("2022-06-09 00:00:00");
 
var dur = GlideDateTime.subtract(gdt1, gdt2);
gs.info(dur.getDisplayValue());

This results as below with day part being omitted. So it seems like GlideDuration do not allow negative days.

*** Script: 11 Hours 40 Minutes

[David Hepburn]

your reply states, "It's a limitation with GlideDuration."

Why is it a limitation with glideDuration when the user is not using glideDuration in the function?