Calculate a Duration Between the Created Date and Today's Date

[Joshua Cassity]

Man oh man.. how frustrating Date/Time calculations can be, am I right?

I built a new table for a customer who's looking to track their build book processes and needs to have four duration fields calculated by the system when he opens a record. I'm thinking if I can get one of them then I can easily get the rest so I've tried a couple of things with no success - see screen below for form layout.

Essentially the customer would like the duration between the system created time stamp (sys_created_on) and the current system date (the problem for me) and to have that duration be shown on the Total Time Open duration field (u_total_time_open).

Has anyone else gone though this kind of nightmare land and can lend a guy some assistance? Did i mention that I really hate date/time calculations.... lol

Bonus Question:   I'm pretty sure I can do the other duration calculations since those fields are actually on the form and already being date stamped but will those duration calculations work if the Created field is a (date/time) and the other fields are date only OR do I need to make them date/time as well?

Thanks in advance.

[drjohnchun]

Please try

var sys_created_on = new GlideDateTime('2016-12-01 00:00:00');
var current_system_date = new GlideDateTime();
var schedule = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828', 'Europe/Paris');
var duration = schedule.duration(sys_created_on, current_system_date);
gs.info(duration.getDisplayValue());

Mixing GlideDateTime and GlideDate

var sys_created_on = new GlideDateTime('2016-12-01 00:00:00');
var current_system_date = new GlideDate();
var schedule = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828', 'Europe/Paris');
var duration = schedule.duration(sys_created_on, current_system_date);
gs.info(duration.getDisplayValue());

works as well.

Hope this helps.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

Winner of November 2016 Members' Choice Award

[Joshua Cassity]

A few things that I'm not for sure on:

1. Why are you creating sys_created_on variable when it would already be on the form itself?

2. Why are you using a schedule? Is that mandatory, because we aren't using any kind of schedules? Probably not good to hardcode that anyway.

[drjohnchun]

Those were given as illustrative examples so you can try them out in Background Script. Once confirmed meeting your needs, you'd use the actual variables and fields instead of the hardcoded values. A Schedule is useful if your duration must be calculated based on, for example, business hours.

If you're trying to calculate a duration on the client side, there's an example from Setting the Duration Field Value - ServiceNow Wiki that you may be able to use:

Add a Client Script:

function onChange(control, oldValue, newValue, isLoading) {
   var strt = g_form.getValue('<start_field>');
   var end = g_form.getValue('<end_field>');
   var ajax = new GlideAjax('AjaxDurCalc');
   ajax.addParam('sysparm_name','durCalc');
   ajax.addParam('sysparm_strt',strt);
   ajax.addParam('sysparm_end',end);
   ajax.getXMLWait();
   var answer = ajax.getAnswer();
   g_form.setValue('<duration_field>', answer);
}

And add a Script Include:

var AjaxDurCalc = Class.create();
AjaxDurCalc.prototype = Object.extendsObject(AbstractAjaxProcessor, {
   durCalc: function() {
       return gs.dateDiff(this.getParameter('sysparm_strt'),this.getParameter('sysparm_end'), false);
   }
});

If all you need to do is to show the duration on form load, you can run this onLoad instead of onChange.

[Joshua Cassity]

My issue when I tried this via that client script was trying to get today's date to do the calculation since that's not an actual field on the form.

Unless I can somehow write a script include to only pull today's date and then set end = todaysDate(); << Script Include Name

What are your thoughts on that?

~ J ~