Field type "schedule_date_time"

[carl_hensley]

I'm trying to write a glide record query against the cmn_schedule_span table and want to restrict the records returned by the start_date_time and the end_date_time on this table. I'm using the data to write a schedule page timeline.

My script is having problems as the start_date_time and end_date_time are of type "schedule_date_time".

I tried this and many variants, but could not get the desired result within the gliderecord:
(for example tried using a GlideDate, etc)

var _userID = '46d44a23a9fe19810012d100cca80666';
var start = new GlideDateTime('2011-10-19 20:00:00');
var end = new GlideDateTime('2012-05-01 07:17:42');
var schedRec = new GlideRecord('cmn_schedule_span');
schedRec.addQuery('schedule.u_schedule_user', '=', _userID);
schedRec.addQuery('Type', '=', 'Time off');
schedRec.addQuery('start_date_time', '<=', end); // constrain the leave to the time bounds of
schedRec.addQuery('end_date_time', '>=', start); // the task we are allocating.
schedRec.query();
while (schedRec.next()) {
.....process the selected records.
}

I then tried to use an encoded query, and was surprised to see the only filter criteria I could use was, "is", "is not" or "is anything".

So given I was suitably stumped, I ended up returning all records for the complete data range, and then used script to only process the records I desired:

var _userID = '46d44a23a9fe19810012d100cca80666';
var start = new GlideDateTime('2011-10-19 20:00:00');
var end = new GlideDateTime('2012-05-01 07:17:42');
var schedRec = new GlideRecord('cmn_schedule_span');
schedRec.addQuery('schedule.u_schedule_user', '=', _userID);
schedRec.addQuery('Type', '=', 'Time off');
schedRec.query();
while (schedRec.next()) {
var leaveStart = schedRec.start_date_time.getGlideObject().getGlideDateTime();
var leaveEnd = schedRec.end_date_time.getGlideObject().getGlideDateTime();
if (( leaveStart <= end) && (leaveEnd >= start)) {
.....process the selected records.
}
}

While my second script provides the correct data, I'm concerned the runtime is longer than it should be, as it is returning more from the database than it needs to.

Can anyone help???

[jtshone]

Any idea why it is failing to correctly convert these dates:

Values on change record:

DEBUG JS: start date = 26/10/2024 08:26:06 PM

DEBUG JS: end date = 30/10/2024 08:26:08 PM

ISO conversion - setting date to the day before:

DEBUG JS: iso start date = 20241025T17000

DEBUG JS: iso end date = 20241029T17000