Need help with the script to calculate the duration between 2 dates

[Rohit8]

Hello,

Wish you all a very Happy New Year.

I am trying to build an Indicator to calculate the Summed age of Assets (to later find out the Average age). The asset table has the columns for Release Date and Retired Date and I need to calculate their difference. The screenshot (A) has the format of the dates.

The 2nd screenshot has the script screen and I am using the above variables. I am not proficient with scripts at all so I am pretty sure I am doing something totally wrong.

var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

var years=function(x,y){return diff(x,y)/(365*24*60*60*1000);};

years(current.u_avg_hw_release_date, current.retired_date_at);

The job returns the error:

Error during JavaScript evaluation com.snc.pa.dc.ScriptException: JavaScript evaluation returned: NaN in script: var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

var years=function(x,y){return diff(x,y)/(365*24*60*60*1000);};

years(current.u_avg_hw_release_date, current.retired_date_at);

at com.snc.pa.dc.Script.handleException(Script.java:215)

at com.snc.pa.dc.Script.evaluate(Script.java:179)

at com.snc.pa.dc.Script.evaluate(Script.java:156)

at com.snc.pa.dc.DataCollector.collect(DataCollector.java:261)

at com.snc.pa.dc.DataCollector.collect(DataCollector.java:214)

at com.snc.pa.dc.DataCollector.collect(DataCollector.java:172)

at com.snc.pa.dc.DataCollectorJob.collect(DataCollectorJob.java:246)

at com.snc.pa.dc.DataCollectorJob.collectWithMutex(DataCollectorJob.java:162)

at com.snc.pa.dc.DataCollectorJob.execute(DataCollectorJob.java:147)

at com.glide.schedule.JobExecutor.executeJob(JobExecutor.java:103)

at com.glide.schedule.JobExecutor.execute(JobExecutor.java:89)

at com.glide.schedule.GlideScheduleWorker.executeJob(GlideScheduleWorker.java:219)

at com.glide.schedule.GlideScheduleWorker.lambda$process$39(GlideScheduleWorker.java:161)

at com.glide.worker.TransactionalWorkerThread.executeInTransaction(TransactionalWorkerThread.java:35)

at com.glide.schedule.GlideScheduleWorker.process(GlideScheduleWorker.java:161)

at com.glide.schedule.GlideScheduleWorker.run(GlideScheduleWorker.java:72)

Caused by: java.lang.Exception: JavaScript evaluation returned: NaN

... 15 more

Can anyone help me correct the script or let me know what am I doing wrong.

Thanks a lot.

[Thilo Grobe]

Hi,

Try to use the native API when working with Date fields. The following code on a dev instance calculates the duration with a demo data asset and two OOTB fields (I put data in them for testing). This sets a GlideDuration object, you can use the getDayPart() method to get the duration broken down into days.

var gr = new GlideRecord('alm_hardware');
gr.get('00a96c0d3790200044e0bfc8bcbe5dc3');
var sgd1 = new GlideDate();
sgd1.setDisplayValue(gr.retirement_date.getDisplayValue());
var sgd2 = new GlideDate();
sgd2.setDisplayValue(gr.retired.getDisplayValue());
duration = GlideDate.subtract(sgd1, sgd2); 
gs.addInfoMessage(duration.getDayPart());

Also make sure your column names are correct in your code, I think the OOTB date field on alm_hardware is "retired" instead of "retired_at_date".

You can put this code into Xplore or run as a background script and put a valid asset sys_id in it for testing.

GlideDate - Scoped

GlideDuration - Scoped

Hope this helps a little,

Cheers,

Thilo

[Rohit8]

Thank a lot, Thilo. I did make an error there with the "Retired" field.

I tested your script in Scripts-Background using the Sys ID of an Asset with the Release Date of 11-Apr-12 and Retired Date of 5-Jan-18

But then the result shows 17536 Days (?) instead of 2095 Days.

Any idea about what could be causing this ? I tried with different Sys IDs but keep getting these off-track numbers as result.

Thanks a lot for your help.

[Shishir Srivast]

Can you please try something like this,

var gr = new GlideRecord('alm_hardware');

gr.get('00a96c0d3790200044e0bfc8bcbe5dc3');

var sgd1 = new GlideDateTime(gr.getValue('retirement_date'));

var sgd2 = new GlideDateTime(gr.getValue('retired'));

var duration = GlideDateTime.subtract(sgd1, sgd2).getDayPart();

gs.print(duration);

[Thilo Grobe]

Hi,

please note that in my code example I compared the "retired" filed to the "retirement_date". In your post you involved one custom field instead. Make sure you put that into my example instead of the "retirement_date".

cheers,

Thilo