Business Rule (after insert/update) - Using a DateTime field in query not returning expected results

[Ruaa1]

Hello,

I have an after insert/update business rule that runs on Schedule Entry [cmn_schedule_span] table. When I try to get the start/end date and time, the time is not coming through correctly. Example:

I select 04-27-2023 9:30:00 AM --> in the log I get 20230427T163000Z
I was expecting to get 20230427T093000

Here's the code I'm using
_____________________

(function executeRule(current, previous /*null when async*/ ) {

var currentAppointments = new GlideRecord("wu_appointment");
gs.info(current.schedule.name);
if(current.schedule.name == "name1"){
locationname = "loc1";
}
if(current.schedule.name == "name2"){
locationname = "loc2";
}
var startDate = current.start_date_time.substring(0, 4)+"-" +current.start_date_time.substring(4, 6) + "-" + current.start_date_time.substring(6, 8);
var startTime = current.start_date_time.substring(9, 11) + ":" + current.start_date_time.substring(11, 13) + ":" + current.start_date_time.substring(13);
var endDate = current.end_date_time.substring(0, 4)+"-" +current.end_date_time.substring(4, 6) + "-" + current.end_date_time.substring(6, 8);
var endTime = current.end_date_time.substring(9, 11) + ":" + current.end_date_time.substring(11, 13) + ":" + current.end_date_time.substring(13);

currentAppointments.addQuery("location.name", locationname);
currentAppointments.addQuery("window_start", ">=", "javascript:gs.dateGenerate('" + startDate + "','" + startTime + "')");
currentAppointments.addQuery("window_start", "<=", "javascript&colon;gs.dateGenerate('" + endDate + "','" + endTime + "')");
currentAppointments.query();
gs.info("current.start_date_time: "+current.start_date_time);
gs.info("current.end_date_time: "+ current.end_date_time);
gs.info(currentAppointments.getEncodedQuery());
gs.info("currentAppointments: "+currentAppointments.getRowCount());
while (currentAppointments.next()) {
var appt = new GlideRecord("sn_apptmnt_booking_appointment_booking");
appt.get(currentAppointments.appointment.sys_id);
if (appt.state != "cancelled") {
appt.state = "cancelled";
appt.update();
gs.eventQueue('eventname', currentAppointments, "", "");
}
}

gs.setRedirect("pagename.do");

})(current, previous);

[DrewW]

That is one of the things that getDisplayValue does, but it also puts it in the users date/time format which could be different than the system default.  So if you want it in the system date/time format but the users time zone use getDisplayValueInternal.

https://developer.servicenow.com/dev.do#!/reference/api/sandiego/server_legacy/c_GlideDateTimeAPI#r_...

[Ian Mildon]

Is there a reason you're not using the "GlideDateTime" API function?

Here is an example of a business rule I created to calculate and set the CAB Meeting value on Change Requests, based on when the Change Request was submitted.

function checkDeadline(){
	var gdt = new GlideDateTime();
	var dayOfWeek = gdt.getDayOfWeekLocalTime(); // Monday = 1
	var nowHour = gdt.getLocalTime(); // noon = 12:00:00

	//Create the html contents of the information message
	var link = '<a href="change_request.do?sys_id=' + current.sys_id + '" class="breadcrumb" >' + current.number + '</a>';
	var message1 = 'Change Request ' + link + ' will be submitted to this weeks CAB meeting.';
	var message2 = 'Change Request ' + link + ' will be submitted to next weeks CAB meeting.';

	//set the CAB date
	var cabDate = new GlideDateTime();
	var now = new GlideDateTime();

	if(dayOfWeek == '6' || dayOfWeek == '7' || dayOfWeek == '1' && nowHour <= '1970-01-01 12:00:00'){ //allow all day on weekends
		cabDate.setValue(now.getInternalMidnight(2));  //sets the cabDate to midnight on tuesday (UTC!!!)
		cabDate.addSeconds(18000); //modify to 5 hours to push us past midnight local time
		current.u_cab_date = cabDate.getValue();
		gs.addInfoMessage(message1);
		_setCabTime();
	} else {
		cabDate.setValue(now.getInternalMidnight(2));  //sets the cabDate to midnight on tuesday (UTC!!!)
		cabDate.addSeconds(18000); //modify to 5 hours to push us past midnight local time
		cabDate.addWeeks(1); //addWeeksLocaltime(1)
		current.u_cab_date = cabDate.getValue();
		gs.addInfoMessage(message2);
		_setCabTime();
	}
}

function _setCabTime(){ //set time to 14:30:00
	var getDate = new GlideDateTime(current.u_cab_date);
	var arrDate = [];
	arrDate = getDate.toString().split(' ');
	current.u_cab_date.setDisplayValue(arrDate[0]+ ' 14:30:00','yyyy-MM-dd HH:mm:ss');
}
checkDeadline();

And as you can see, each of the time/date vars are specifically defined. I even go as far as over-riding the "time" value for the output to make it display as "14:30"

[Ruaa1 · Accepted solution]

Thank you, @DrewW & @Ian Mildon. This is how I got it to work.

var start = current.start_date_time.getDisplayValue();
var end = current.end_date_time.getDisplayValue();
var s = convertTo24(start);
var e = convertTo24(end);

currentAppointments.addQuery("location.name", locationname);
currentAppointments.addQuery("window_start", ">=", "javascript&colon;gs.dateGenerate('" + s[0] + "','" + s[1] + "')");
currentAppointments.addQuery("window_start", "<=", "javascript&colon;gs.dateGenerate('" + e[0] + "','" + e[1] + "')");
currentAppointments.query();

function convertTo24(d) {
var dateTimeArray = d.split(" ");
var time = "";
var hr = 0;
var hrArray = [];
var dArray =dateTimeArray[0].split("-");
var date = dArray[2]+"-"+dArray[0]+"-"+dArray[1];

if (dateTimeArray[2] === "AM") {
hrArray = dateTimeArray[1].split(":");
hr = parseInt(hrArray[0]);
if (hr === 12) {
hr = "00";
}
time = hr + ":" + hrArray[1] + ":" + hrArray[2];
}

if (dateTimeArray[2] === "PM") {
hrArray = dateTimeArray[1].split(":");
hr = parseInt(hrArray[0]);
if (hr != 12) {
hr = parseInt(hrArray[0]) + 12;
}
time = hr + ":" + hrArray[1] + ":" + hrArray[2];
}

var results = [];
results.push(date);
results.push(time);

return results;
}