Function Field Datedif()

kengeeoh
Tera Contributor

Appologies if this is a silly question. I'm new.
I am trying to derive "days" lapsed between approval set and closed on the change table.

Using:
     glidefunction:datediff(closed_at,approval_set) the result is 1,970
     glidefunction:datediff(approval_set,closed_at) the result is 1,969
     The correct answer is 1. 

 

I'm using return type = decimal  (I also tried whole number and duration).

I also tried dividing the results by 86400 in case it's stored in seconds with no change in output.
What am I doing wrong?  

3 REPLIES 3

Rafael Batistot
Tera Sage

Hi @kengeeoh 

What’s happening is that glidefunction:datediff isn’t actually giving you the “number of days” you expect — it’s either working in seconds or interpreting the values in a way that produces huge numbers.

If you want the exact number of days between approval_set and closed_at on the change_request table, here’s an example in a script (Background Script):

var gr = new GlideRecord('change_request');
if (gr.get('<change_sys_id>')) {
    var approvalSet = new GlideDateTime(gr.approval_set);
    var closedAt = new GlideDateTime(gr.closed_at);

    var duration = GlideDateTime.subtract(closedAt, approvalSet); // GlideDuration
    var daysDiff = duration.getNumericValue() / (1000 * 60 * 60 * 24); // ms → days

    gs.print("Days difference: " + daysDiff);
}



Nikhil Bajaj9
Giga Sage

Hi @kengeeoh ,

 

No question is a Silly or dumb question. It is only silly if you will not ask it and you will not learn it so don't worry. Answer already given by Rafael.

 

Regards,

Nikhil Bajaj

Please appreciate my efforts, help and support extended to you by clicking on – “Accept as Solution”; button under my answer. It will motivate me to help others as well.
Regards,
Nikhil Bajaj

AbhishekLawaniy
Tera Contributor

Building on the example provided by Rafael, If you want the difference to be absolute number of days, you may use following script:

 

 

 

var gr = new GlideRecord('change_request');
if (gr.get('Record sys id here')) {
    var approvalSet = new GlideDateTime(gr.approval_set);
    var closedAt = new GlideDateTime(gr.closed_at);

    var diffMillis = Math.abs(approvalSet.getNumericValue() - closedAt.getNumericValue());

    var daysDiff = diffMillis / (1000 * 60 * 60 * 24);
    var absoluteDays = Math.round(daysDiff);
    
	gs.print("daysDiff : " + daysDiff);
    gs.info("Absolute Difference in days: " + absoluteDays);
}

 

 

Regards,
Abhishek Lawaniyan
=========================================================
If my response proves useful, please indicate its helpfulness by selecting "Accept as Solution" and " Helpful." This action will support the community.