How to use 2 dates parameters as a range or with between operator in Encoded query?

[Virendra K]

Hi All,

I am using below query with "start date time" and "end date time". I need to check the range from 2 dates from 2 different column, but query is not working as expected.

I see the "start date time" output is stored as  20240408T044500Z, so I tried with below query as well but no luck,

schedule.type=blackout^schedule.name=Central Winter Break^start_date_time>=20240917T000000^end_date_time<=20240918T235959

Using >= and <= is the correct approach to fetch date range from 2 columns ?

            var output=[];

             var gr1 = new GlideRecord('cmn_schedule_span');

             var query2= "schedule.type=blackout^schedule.name=Central Winter Break^start_date_time>=javascript&colon;gs.dateGenerate('2024-09-16','09:08:46 ')^end_date_time<=javascript&colon;gs.dateGenerate('2024-09-17','09:08:46')";

            gr1.addEncodedQuery(query2);

            gs.print('Query= ' + query2);

            gr1.query();

            while (gr1.next()) {

                gs.print("Passed");

                output.push(gr1.name+ ' -- '+ gr1.start_date_time + ' to ' + gr1.end_date_time);

                }

           gs.print('Output is ' + output);

Thanks,

VIrendra

[Cesar Salas1 · Accepted solution]

Hi @Virendra K 

Usually, that is the best approach for Date/Time fields, but in this particular case the fields used in the table are not date/time, they are Schedule Date/Time, if you desire to use them as a range in your query it is a better idea first getting the display value from the 'start_date_time' and the 'end_date_time' and then 'create' the parameters for the query, this is an example:

var schedule = new GlideRecord('cmn_schedule_span');
schedule.addEncodedQuery('schedule=83cd9a930a0a2c3e6d4957fe9651c24d');
schedule.query();
if (schedule.next()) {
    var start = schedule.getDisplayValue('start_date_time');
    var end = schedule.getDisplayValue('end_date_time');
    gs.info(start);
    gs.info(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.info(startDateStr[0] + ' - ' + startTimeStr[1]);
	gs.info(endDateStr[0] + ' - ' + endTimeStr[1]);
    var inc = new GlideRecord('incident');
    inc.addEncodedQuery("sys_created_on>=javascript&colon;gs.dateGenerate('" + startDateStr[0] + "','" + startTimeStr[1] + "')^sys_created_on<=javascript&colon;gs.dateGenerate('" + endDateStr[0] + "','" + endTimeStr[1] + "')");
    inc.query();
    while (inc.next()) {
        gs.info(inc.number + ' - ' + inc.sys_created_on);
    }

I hope you find it helpful.

Regards,

-Cesar Salas