Between date query on two dates

[Nikhil Gadodiya]

I have custom table where in we have 2 date and time field start and end as:

Now when user submits the new record I want to check if the entered start and end time slot is empty or not similar to that of the calendar.

How can I check that using GlideRecord Query.

[Mark Roethof]

So what exactly didn't work?

Is the first if reached at, the one with b.next? Did you debug that?
Is the gs.info on a.getRowCount reached? Did you debug that?
Did you debug if for example b.u_event_end_date returns what you would expect?
Are there any errors in the logs?

Also looking at:
a.addEncodedQuery("u_event_start_dateBETWEENjavascript:b.u_event_start_date@javascript:b.u_event_end_date");

Try b.u_event_start_date and b.u_event_end_date outside of quotes. For example:

a.addEncodedQuery("u_event_start_dateBETWEENjavascript:" + b.u_event_start_date + "@javascript:" + b.u_event_end_date);

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020 ServiceNow Community MVP
2020 ServiceNow Developer MVP

---

LinkedIn
Community article list

[Tanaji Patil]

Replacing date values as string in copied encoded query never worked for me with dates. Try passing GlideDate object. Try this code.

var b = new GlideRecord("u_multimedia");
b.addQuery("sys_id","21a214861b63041092b4fc88cc4bcbd9");
b.query();
if(b.next()){
var gdtStart = b.u_event_start_date.getGlideObject();
var gdtEnd = b.u_event_end_date.getGlideObject();

var a = new GlideRecord("u_multimedia");
a.addEncodedQuery("u_event_start_dateBETWEEN" + gdtStart + "@" + gdtEnd);
//a.addEncodedQuery("u_event_start_dateBETWEENjavascript:gs.dateGenerate('2020-03-17','00:00:00')@javascript:gs.dateGenerate('2020-03-18','00:00:00')");
a.query();
gs.info(a.getRowCount());
}

-Tanaji
Please mark response correct/helpful if applicable

[Sateesh Kumar 2]

Hello,

I understand it this way, whenever a user tries to register a new event you need to check existing table if it is conflicting with existing events already registered.

If that is the requirement there could be several scenarios, for example

1) if event exists from 17/3/2020 to 19/3/2020 and new event registered is for 18/3/2020 to 20/3/2020 should this be allowed?

2) If existing event is from 17/3/2020 to 19/3/2020 and new event is from 18/3/2020 to 19/3/2020 and much more scenarios.

I give you a query for restricting 2nd scenario, you can manipulate the query for other scenarios.

validateEventDates:function(newEvenStartDate,newEvenEndDate){

var gr= new GlideRecord("u_event_table");

gr.addQuery("event_start_date","<=",newEvenStartDate);

gr.addQuery("event_end_date",">=",newEvenEndDate);

gr.query();

if(gr.next())

{

return false;

}

return true;

},

Regards,

Sateesh Kumar Devadoss.

[Nikhil Gadodiya]

Hi Sateesh,

Any overlap it should alert me, like in calendar.

[Mark Roethof]

Hi there,

Did this solve your question? Or do we need to follow-up on this?

Please mark this answer as correct if it solves your question. This will help others who are looking for a similar solution. Also marking this answer as correct takes the post of the unsolved list.
Thanks.

Kind regards,
Mark
2020 ServiceNow Community MVP
2020 ServiceNow Developer MVP

---

LinkedIn
Community article list