Date Conversion Problems

[John Prouty]

I have a Flow Designer action:

My problem is that for some reason, the format of the two input variables (start_date and end_date) are not the same format.  I need someway to convert the dates into a consistent format so that the duration calculation will work properly.

Here is the input and output of the action:

the two dates: 12-7-2020 11:33:55 and 12-902020 17:44:53 are 2 days, 6 hrs, 10 min, 58 secs, NOT 11,324 days, 22 hrs, 10 min, 58 secs.

I should probably has logic in my action to detect which format each input variable is in, but I don't know how to do that.

[John Prouty]

your code is equivalent to mine.  I tried it anyway and I get the same results.  Got any other ideas?

[asifnoor]

I think the format of system date/time looks to be different than the conversion that you are doing. 

your system date/time format is yyyy-mm-dd where as the values you got are in dd-mm-yyyy. Check if that is causing any issue.

[John Prouty]

I have done enough testing to determine that my problem is not a data format issue.  GlideDateTime seems to be smart enough to handle dates of a variety of formats.  I get the same results whether I use YYYY-MM-DD format of MM-DD-YYYY format.  My problem is that two dates that are 2 days apart are generating a duration of 11,354 days!  If I use a different schedule I do get a different duration, but it is 11,324 days (both are unreasonable).  I have tried base calendars (that came with the developer instance), so they are calendars deployed by ServiceNow.

Here is my Action:

(function execute(inputs, outputs) {

   var schedule = new GlideSchedule();
  schedule.load(inputs.schedule_sys_id);

  var starting_date = new GlideDateTime(inputs.start_date);
  var ending_date = new GlideDateTime(inputs.end_date);

  outputs.starting_date = starting_date;
  outputs.ending_date = ending_date;
  var duration = schedule.duration(outputs.starting_date, outputs.ending_date);
  outputs.duration = duration.getDurationValue();

   //outputs.message = "Starting Date: Yr-" + starting_date.getYearLocalTime() + ", Mth-" +     starting_date.getMonthLocalTime() + ", Day-" + starting_date.getDayOfMonthLocalTime() +
    "; Ending Date: Yr-" + ending_date.getYearLocalTime() + ", Mth-" + ending_date.getMonthLocalTime() +     ", Day-" + ending_date.getDayOfMonthLocalTime() + ".";

})(inputs, outputs);

The message output was for testing purposes only (to see the internal values of dates).

Any ideas of why 11-02-2020 08:00:00  and 11-04-2020 09:00:00 generates such a large duration (11,324 days)?

[asifnoor]

Hi,

I just did 1 quick test on background script using

var gdt = new GlideDateTime("01-01-1970 08:00:00");
var gdt1 = new GlideDateTime("11-04-2020 09:00:00");
gs.print(gs.dateDiff(gdt,gdt1,false));
var schedule = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828');  (schedule excluding weekends)
var duration = schedule.duration(gdt,gdt1);
gs.print(duration.getDurationValue());

and the result is 

*** Script: 18570 01:00:00 (without any scheudle)
*** Script: 13081 15:00:00 (with schedule)

So if you notice I have taken start date/time as 1970/01/01 which is the default start date/time SN considers when the format is not correct.

Looking at this i still feel that somewhere there is issue with the format of date/time. 

Can you run this script in your background using yoru schedule id and those 2 dates and check once.

[John Prouty · Accepted solution]

I could only test the script in Flow Designer.  dateDiff doesn't work in FlowDesigner.  I replaced it with Subtract.

Here is the final/working code:

(function execute(inputs, outputs) {

     var starting_date = new GlideDateTime(inputs.start_date);
     var ending_date = new GlideDateTime(inputs.end_date);
     var time_zone_adj = new GlideTime();
     time_zone_adj.setValue("08:00:00");
     starting_date.add(time_zone_adj);
     ending_date.add(time_zone_adj);

     var schedule = new GlideSchedule(inputs.schedule_sys_id);
     var duration = schedule.duration(starting_date, ending_date);
     //outputs.message = "Schedule Duration: " + duration.getDurationValue();

     outputs.starting_date = starting_date;
     outputs.ending_date = ending_date;
     outputs.duration = duration;

})(inputs, outputs);

One thing I figured out is that my dates were Local Timezone and GlideDateTime assumes they are UTC.  So I had to add 8 hours to the start and end dates.

I also dropped the getDurationValue from the assignment of outputs.duration.

ServiceNow is good at converting dates of various formats to a standard time.