Assistance Needed: Overlapping Records Query Returning Incorrect Dates in Scripts – Background

[Lisa Goldman]

Hello,

I'm using Scripts – Background to query how many records have overlapping dates based on the start and end date parameters from a Service Request. The intended behavior is that if the start and end dates fall within an existing record, it should not return that record. I am aware that this record already exists.

At the moment, I’m manually entering the dates:

var workStart = '02-04-2026 12:33:16 PM';
var workEnd = '02-07-2026 04:33:19 PM';

I suspect the date and time format in the table is different from the format I’m passing in. Could someone provide suggestions on how to handle this properly?

Thank you!

// Start and End Dates are from catalog variable
var workStart = '02-04-2026 12:33:16 PM';
var workEnd   = '02-07-2026 04:33:19 PM';

var gr = new GlideRecord('x_equipment_request');

// Overlap: work_start < window end AND work_end > window start
gr.addEncodedQuery(
    'work_start<' + endGdt.getValue() +
    '^work_end>' + startGdt.getValue() +
    '^state!=Closed Complete'
);
gr.query();

var count = 0;
while (gr.next()) {
    count++;

    gs.info('work_start=' + recordStart +
            ' | work_end=' + recordEnd +
            ' | state=' + gr.getDisplayValue('state'));
}

[WillieW]

Where is 'endGdt' and 'startGdt' defined?

[Lisa Goldman]

Hi @WillieW 

Thank you for pointing out the mistake. I’ve revised the code slightly, but I still can’t figure out where I went wrong.

// Start and End Dates are from catalog variable
var workStart = '02-01-2026 12:33:16 PM';
var workEnd   = '02-02-2026 04:33:19 PM';

var gr = new GlideRecord('x_equipment_request');

var startGdt = new GlideDateTime(workStart);
var endGdt   = new GlideDateTime(workEnd);

// Overlap: work_start < window end AND work_end > window start
gr.addEncodedQuery(
    'work_start<' + startGdt.getValue() +
    '^work_end>' + endGdt.getValue() +
    '^state!=Closed Complete'
);
gr.query();

var count = 0;
while (gr.next()) {
    count++;
    gs.info(count);
}

[Lisa Goldman]

I believe I’ve resolved the issue. The problem was a mismatch between the date formats:

• work_start in the table: 2026-02-04 20:33:16 - stored in internal UTC format

• Parameter from the catalog: 02-01-2026 12:33:16 PM - display format

Using GlideDateTime(workStart) alone works, but when .getValue() is used in the query, ServiceNow compares internal UTC values. If the user’s timezone isn’t UTC, or the string is in 12-hour format, the overlap check fails and no records are returned.