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]

You are not dealing with the time zone correctly.  When ever the system runs code it does it in GMT.  When the user selects a date/time its in the time zone that is set on there user record and if no time zone is set on the user record it uses the system time zone.  So if you need to do your query in the users time zone then you need to use the display value of the data/time when you do your conversion.  So something like

var myStartDate = current.start_date_time.getDisplayValue();

var startDate = myStartDate.substring(0, 4)+"-" +myStartDate.substring(4, 6) + "-" + myStartDate.substring(6, 8);

is there some reason you did not just do

currentAppointments.addQuery("window_start", ">=", current.start_date_time);

currentAppointments.addQuery("window_start", "<=", current.end_date_time);

[Ruaa1]

Hello @DrewW ,

Thank you for your response.

I originally tried it with current.start_date_time.getDisplayValue(). This will return a string like this 04-27-2023 9:30:00 AM. I couldn't use this value in my query. So I tried to get GlideDateTime record from it, but then the time comes back wrong. For Example: 12:00:00 AM will come back as 5:00:00 and 11:59:59 PM will come back as 4:59:59.

is there some reason you did not just do

currentAppointments.addQuery("window_start", ">=", current.start_date_time);

currentAppointments.addQuery("window_start", "<=", current.end_date_time);

I tried above code, but the query doesn't work. current.start_date_time returns this format 20230427T163000Z.

Best regards,

Ruaa

[DrewW]

What is the field data type for the start_date_time and window_start fields?

It looks like a time zone issue.  All backend GlideDateTime and GlideDates are stored in GMT.  You can show this by running 

var dt = new GlideDateTime();

gs.print(dt);

as a background script.  For me it returns 

2023-04-26 18:42:00

because its 2:42pm EST and the above is GMT.

Does your instance use a standard time format and have you changed the time format that your account used?

[Ruaa1]

@DrewW, I ran the background script and I'm getting the correct time in GMT. Do I need to convert from GMT to Pacific (my time)?

Best regards,

Ruaa