How to check date range using IF condition ?

[Virendra K]

Hi All,

The requirement is, I need to show the moratorium conflicts passing planned start and End date. I am gliding on "cmn_schedule_span" table to check the date range for "Start Time" and "End Time" columns (refer snaps).

I tried to achieve this using below encoded query, but not working in some scenarios,

" schedule.type=blackout^u_start_time>=javascript&colon;gs.dateGenerate('2024-09-23','00:00:01')^u_end_time<=javascript&colon;gs.dateGenerate('2024-09-24','23:59:59')"     ----->> I am getting above dates from UI (Planned start and end date fields)

Like, if I am using above query with date range 23 to 24 Sept and I do have a record "Central MLK Orange" with

Start Time = "2024-09-23 05:15:00 PM"

End Time = "2024-09-25 09:00:00 PM"  (refer snap)

But query is not working and fetching "Central MLK Orange" record because its "End Time" is 25th Sept.

Logically it should fetch the record because "Central MLK Orange" is having the date range from 23 Sept to 25 Sept which includes 24th Sept as well.

so I am trying to achieve this using IF condition. I want to pass date range (planned start and end date) and check the range with Start and End Time columns, if its within the range then I will push the fetched record in an array.

How I could use/pass the date range in IF condition and check it with Start Time and End TIme columns ?

Regards,

Virendra

[Community Alums]

Hi @Virendra K 

Can you please share your script?

Regards,

Krishna 

[Virendra K]

Hi @Community Alums 

Currently I am trying below code.

var output = [];

var gr1 = new GlideRecord('cmn_schedule_span');

var query2 = "schedule.type=blackout^name=Central MLK Orange";

gr1.addEncodedQuery(query2);

gs.print('VIREN : Q2 = ' + query2);

gr1.query();

var stdate;

var endate;

 gs.print("VIREN Count " + gr1.getRowCount());

while (gr1.next()) {

    stdate = gr1.getValue("u_start_time");

    //stdate = new GlideDateTime((stdate));

    endate = gr1.getValue("u_end_time");

    //endate = new GlideDateTime((endate));

    gs.print("VIREN Dates " + stdate +'  '+ endate);

    if(( stdate >="2024-09-23 00:00:01" && endate <= "2024-09-24 11:59:59")   ){

      gs.print("VIREN Passed");    

      output.push(gr1.name + ' -- ' + stdate + ' to ' + endate + '\n');

    }

    gs.print('VIREN TESTING Result Before Output is \n' + output);

}

=================================================================================

Earlier it was using query like below,

var start;
var end;
var schedule = new GlideRecord('cmn_schedule_span');
schedule.addEncodedQuery('schedule.type=blackout^name=Central MLK Orange');
schedule.query();
if (schedule.next()) {
start = schedule.getDisplayValue('u_start_time');
end = schedule.getDisplayValue('u_end_time');
gs.print(start);
gs.print(end);
var startDateTime = new GlideDateTime();
startDateTime.setDisplayValue(start);
var endDateTime = new GlideDateTime();
endDateTime.setDisplayValue(end);
var startDateStr = startDateTime.toString().split(' ');
var startTimeStr = startDateTime.toString().split(' ');
var endDateStr = endDateTime.toString().split(' ');
var endTimeStr = endDateTime.toString().split(' ');
gs.print(startDateStr[0] + ' - ' + startTimeStr[1]);
gs.print(endDateStr[0] + ' - ' + endTimeStr[1]);

var gr = new GlideRecord('cmn_schedule_span');
var query1="schedule.type=blackout^u_start_time>=javascript&colon;gs.dateGenerate('" + startDateStr[0] + "','" + startTimeStr[1] + "')^u_end_time<=javascript&colon;gs.dateGenerate('" + endDateStr[0] + "','" + endTimeStr[1] + "')";
gs.print('query - ' + query1);
gr.addEncodedQuery(query1 );
gr.query();
while (gr.next()) {
gs.print('Name- ' + gr.name);
}
}

[Ravi Gaurav · Accepted solution]

Hi @Virendra K 

use the below revised code 

var output = [];
var gr1 = new GlideRecord('cmn_schedule_span');
var query2 = "schedule.type=blackout^name=Central MLK Orange"; // Your query to filter by blackout and name
gr1.addEncodedQuery(query2);
gs.print('VIREN: Query = ' + query2);
gr1.query();

var plannedStart = new GlideDateTime('2024-09-23 00:00:01'); // Planned start date
var plannedEnd = new GlideDateTime('2024-09-24 23:59:59'); // Planned end date

gs.print("VIREN: Record Count = " + gr1.getRowCount());

while (gr1.next()) {
var stdate = new GlideDateTime(gr1.getValue("u_start_time")); // Convert start time to GlideDateTime
var endate = new GlideDateTime(gr1.getValue("u_end_time")); // Convert end time to GlideDateTime

gs.print("VIREN: Record Start Date = " + stdate.getDisplayValue() + " | End Date = " + endate.getDisplayValue());

// Check if the record falls within the planned range
if (stdate <= plannedEnd && endate >= plannedStart) {
gs.print("VIREN: Passed - Record within date range.");
output.push(gr1.name + ' -- ' + stdate.getDisplayValue() + ' to ' + endate.getDisplayValue() + '\n');
}

gs.print('VIREN: Current Output = \n' + output);
}

gs.print('VIREN: Final Output = \n' + output);