How to compare dates in a GlideRecord query

[Shane18]

Hi, I'm trying to compare Date fields (not date/time, just date fields). I want it to only get me records where the start dates are after the current end date, or where the end dates are after the current start date. Basically, to ensure no other bookings occur on these dates. Code below:

var gr_getBookings = new GlideRecord('x_210272_insta_ava_instance_booking');
gr_getBookings.addQuery('name_of_instance', current.name_of_instance);
//Verify there are no bookings where the start date is before the current end date, or where the end date is after the current start date.
gr_getBookings.addQuery('start_date', "<=", current.end_date).addOrCondition('end_date', '>=', current.start_date);

gr_getBookings.query();
while(gr_getBookings.next()) {

//do this

}

However, the while loop always runs, even if there are no records within the date range?

[Shane18]

This is what I came up with:

gr_getBookings.addQuery('start_date', "<=", current.start_date).addCondition('end_date', '>=', current.start_date).addOrCondition('end_date', '>=', current.end_date).addCondition('start_date', '<=', current.end_date);

However, it doesn't appear to be working.

[Anurag Tripathi]

Its not working because you are comparing the same dates. Can you show me screenshot of the form and then describe what exactly is the requirement

[Shane18]

I've posted screenshots.

[Shane18]

[gkishen84]

Hi Shane,

Can you try this out, tried using Encoded Query

var gr_getBookings = new GlideRecord('x_210272_insta_ava_instance_booking');
gr_getBookings.addQuery('name_of_instance', current.name_of_instance);
//Verify there are no bookings where the start date is before the current end date, or where the end date is after the current start date.

gr_getBookings.addEncodedQuery('start_date<=' + current.end_date + '^start_date>=' + current.start_date + '^NQend_date<=' + current.end_date + '^end_date>=' + current.start_date);

gr_getBookings.query();
while(gr_getBookings.next()) {

//do this

}

Regards,

Kishen