Building Scripted Date/Time Conditions (Last Business Day, Next 3 Business Days, etc)

JosephW1 · ‎05-20-2020

Hello,

INTRODUCTION

I would like to share with the community a way to use custom JavaScript when building query conditions for Date/Time fields. This is something that I have personally created 3 unanswered questions for and done lots of searching for over the last few months, so I am quite excited to share a solution for others to use. 🙂 (Thank you to all who helped out along that adventure! Much appreciated.)

You might be asking "Why would I even care about this? I have the relative filter." And that's fair, I agree that relative is very useful. But there are times when it doesn't cut it.

For example, suppose you are building a time series column chart trending by opened per week. Your stakeholder wants a report to show the totals of the last 3 weeks grouped by whole weeks, Mon-Sun. So, suppose you add a "Opened > relative > on or after 21 days ago" condition. Now, suppose that the stakeholder using this report needs to be able to load it from a dashboard any given time of the week. Monday, Tuesday, Friday, you name it. We will run into a few problems, as shown in the image below.

1) Since the relative operator returns a rolling time period, it will most likely show 4 bars when ran, as shown above. Okay, so add a "Opened > not on > This week" condition. All done? No, because you'll still have the second problem. 2) The first bar - and the 4th bar if you don't add the condition I just mentioned - will be an impartial representation of its corresponding week. It will not be Mon 00:00:00 - Sun 23:59:59. This means that oftentimes it will result in false narratives, showing a significant downward trend on the tail ends of the report that do not actually exist in the time range the customer actually wants to examine, given that the customer wants the freedom of being able to load this report anytime he wants. (We're not even giving the customer what he actually wants, oops!)

Shown below is what that report is actually supposed to look like according to the stakeholder's requested time condition. However, this required the use of a hard-coded date/time condition. (I don't know about you, but those make me cringe...)

So, I don't know about you, but I don't see any difference. Nope, nada, both reports above are giving the exact same narrative, so why bother?! Hahaha, just kidding! There's a huge difference! This second one doesn't show that superficial significant downward trend on the tail ends, does it?

By the way, we apparently can't edit or add any extra choices to the condition builder's list of date/time filters. Apparently it's locked down.

Man... Um... Then how do we do this without using hard-coded date/time conditions? What do we do? Give up?

WHERE THERE'S A WILL

The way I found to call your own custom JavaScript on a condition for a date/time field is to place it in the query via URL manipulation. Load the incident table and quickly build & run a plain "Opened > on > Today" filter. You'll get the following URL.

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()%26sysparm_first_row%3D1%26sysparm_view%3D

If you'll take a look at the highlighted sections of the URL, you'll notice:

the display name you see in the condition builder,
the script for the beginning date/time for the query, and
the script for the end date/time of the query.

In case you don't know, those scripts are GlideSystem GlideDateTime JavaScript scripts stored in ServiceNow. That's why they start with "gs." - gs stands for GlideSystem! Fortunately, the gs.[units]AgoStart() and gs.[units]AgoEnd() functions accept integers! This means we can call them with custom integers to make our own non-standard Date/Time scripted condition.

Here's our options:

gs.minutesAgoStart()
gs.minutesAgoEnd()
gs.hoursAgoStart()
gs.hoursAgoEnd()
gs.daysAgoStart()
gs.daysAgoEnd()
gs.weeksAgoStart() - missing
gs.weeksAgoEnd() - missing
gs.monthsAgoStart()
gs.monthsAgoEnd()
gs.quartersAgoStart()
gs.quartersAgoEnd()
gs.yearsAgoStart()
gs.yearsAgoEnd()

We'll make our own custom scripts for weeksAgoStart and weeksAgoEnd in a moment. We'll make them function the same way the gs variants do.

However, first, here's how they work.

weeksAgoStart(3) performs two steps. The first step is that it goes back 3 x 7 days. If it's 5/22/2020 10:49:30, it goes back to 5/1/2020 10:49:30. You might be thinking it's similar to relative at this point, but don't forget the second step, which relative doesn't provide. The second step is that, from that point, it then goes back to the Monday of that week at 00:00:00. That is the beginning of the week.

weeksAgoEnd(1) also performs two steps. The first step is the same: it goes back 1 x 7 days. The second step is that, from that point, it then goes forward to the Sunday of that week at 23:59:59. That is the end of the week.

A combination of the two JavaScript functions above would give us the entire last 3 weeks, Mon-Sun of each week.

LET'S MAKE A WEEK VARIANT

Here are two scripts I wrote that imitate what gs.weeksAgoStart and gs.weeksAgoEnd would do, if they existed in the first place.

(I'm not sure why they omitted the week variants. I'm guessing that this family of scripts was created with the relative filter in mind and, since relative omits weeks as a time unit, no weeks variant of these scripts was created. Or maybe relative omitted weeks because the scripts were missing? Or maybe they thought they would be too week? (haha, get it?) Anyways, it's not important right now.)

So, these will allow us to build conditions based on week start and end points. Just create these as client callable Script Includes.

(These have been updated to honor the logged in user's timezone the same way the OOTB date/time filters do.) -06/05/2020

Object:	Script Include
Name:	weeksAgoStart
Application:	Global
Accessible from:	This application scope only
Client callable:	TRUE
Description:	Script include for use in query time conditions via list-view URL manipulation. Finds the start of n number of weeks ago. gs.weeksAgoStart is not available OOTB.
Script:	function weeksAgoStart(n){ var gdt = new GlideDateTime(); //SET THE DATE gdt.addWeeksLocalTime(n * -1); gdt.addDaysLocalTime( ( gdt.getDayOfWeekLocalTime() - 1 ) * -1 ); //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 00:00:00"); //RETURN THE DATE return gdt; }

Object:	Script Include
Name:	weeksAgoEnd
Application:	Global
Accessible from:	This application scope only
Client callable:	TRUE
Description:	Script include for use in query time conditions via list-view URL manipulation. Finds the end of n number of weeks ago. gs.weeksAgoEnd is not available OOTB.
Script:	function weeksAgoEnd(n){ var gdt = new GlideDateTime(); //SET THE DATE gdt.addWeeksLocalTime(n * -1); gdt.addDaysLocalTime( 7 - gdt.getDayOfWeekLocalTime() ); //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 23:59:59"); //RETURN THE DATE return gdt; }

UMM, WHAT NOW?

So, let's run that simple incident query again. The "Opened > on > Today". We'll get the same URL.

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()%26sysparm_first_row%3D1%26sysparm_view%3D

This time, let's manipulate the URL. Change it to this:

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONLast 3 weeks@javascript:weeksAgoStart(3)@javascript:weeksAgoEnd(1)%26sysparm_first_row%3D1%26sysparm_view%3D

(Since we're using our own custom script include - not the GlideSystem library - we don't use the gs prefix here!)
After you finish typing that in, press Enter to navigate to your edited URL. This will import your new custom date/time condition into the condition builder, and even apply the custom label you gave it!

I think I might know what you're thinking. "Okay, so how do I do this for reports?" Well, first we have to build the list-view query. After that, we import the query into the report designer via the context menu. Just choose "Bar Chart" or "Pie Chart" to pull the query into the report designer.

Here's what you'll end up with.
(I changed my query to "Activity due > on > Last 3 weeks" just to get results in my PDI.)

Now just Save the report to store that condition for later use. You will have to build all your custom-script Date/Time conditions from the list-view and import them into the report designer via this method. Save them to serve as templates for future reports using the same custom condition. Not ideal - pretty technical for some users - but this is really useful!

Take it a step further. Save your report as a report source! This accomplishes one small task and a second much larger task. First, it makes it easier to use these in reports in the future, and second, it makes these usable from Datasets and PA Indicators! -06/07/2020, 06/18/2020

( I've noticed that these queries are successfully preserved as 1) ServiceNow & browser favorites, 2) copy & pasted URLs, 3) imported into reports, and 4) saved as report sources. However, they don't work as saved filters [sys_filter]. The filter distorts the custom Date/Time condition by replacing the __ section of "javascript:_____()" with gs.dateGenerate, and also places the label in the (). Minor detail, just don't try to save these as filters. Save them as favorites, reports, report sources, or copy & pasted URLs instead. 🙂 )

BACK TO THE EXAMPLE

So, back to the example from the introduction.

Armed with these new custom scripted date/time conditions, this means that now we can make the "Last 3 weeks" - a.k.a. last 3 full weeks, Sun-Mon - report that our stakeholder requested without using hard-coded dates in the date/time condition! That means we won't have to rebuild the report's date/time condition every week if we put it on a dashboard, nor will we possibly have to strip it from the dashboard & instead schedule the report to run on Sunday at 23:59:59. Now we can place this puppy on the dashboard with confidence it will work no matter when he loads it! High-five!

I think it is so much more convenient this way.

MORE EXAMPLES

Not only that, but so many new options are available now, too.

2nd to Last Week
3rd to Last Week
4th to Last Week
Last 2 Weeks
Last 3 Weeks
Last 4 Weeks
2nd to Last Month
3rd to Last Month
4th to Last Month
5th to Last Month
6th to Last Month
Last 2 Months
the list goes on and on, but those are among the ones I plan to use

To demonstrate the bold items in the above list, here's a visual.

Shown above is a dashboard that has monthly score cards for the last 6 months. Except for the top-right single-score report titled "1st Month", soft-coding these report's conditions is not possible OOTB using just the condition builder. That's right, the reports with highlighted titles are all using URL-manipulated custom date/time conditions that would likely otherwise have to be hard-coded dates. (Stop saying that word, Joseph. "hard-coded dates". Stop!)

Here is a convenience sample query for the "2nd Month" report:

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atON2nd to Last Month@javascript:gs.monthsAgoStart(2)@javascript:gs.monthsAgoEnd(2)%26sysparm_first_row%3D1%26sysparm_view%3D

Following this pattern, you can create the queries used for the other 4 reports. Combine these reports with some interactive filters and you get some very flexible score widgets. 🙂

ANOTHER EXAMPLE

In fact, by utilizing another custom script, we can even have a Last Business Day condition now! I'll throw those scripts in free, too, as I don't think they are available OOTB. (Are they?) Here they are, they operate on whatever GlideSchedule that you pass to them. This is useful if you need to be able to report on Friday when you load the report on Sat-Mon, but switch to reporting on yesterday when you load the report on Tues-Friday.
(These have been updated to 1) work based on GlideSchedules, 2) work with any # of bdays, 3) work for past or future dates, 4) not count holidays as bdays.) -06/02/2020
(Updated again to 5) honor the user's timezone, and 6) have a better infinite loop fail safe.) -06/05/2020
(Updated again to 7) honor the system's date/time format and the user's time zone, to remain compatible with the system no matter the user's date/time format.) -09/17/2020
(Updated again to 😎 work from all application scopes since users will likely want to use this filter across multiple scopes.) -09/18/2020

Object:	Script Include
Name:	bdaysAgoStart
Application:	Global
Accessible from:	All application scopes
Client callable:	TRUE
Description:	Script include for use in query time conditions in order to find & set the start (00:00:00) of n number of "Business Days" ago. A current goal is to return the "business day" in the user's time zone, to provide expanded compatibility for service desks to query based on their service desk's time zone.
Script:	function bdaysAgoStart(n){ var gdt = new GlideDateTime(); var sched = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828'); //8-5 Excluding Holidays, floating timezone var i = n > 0 ? -1 : 1; //if positive n subtract days, otherwise add var lfs = 170; //Loop Fail Safe: prevents massive loops while allowing up to ~1 year query //CALIBRATE TO A BUSINESS HOUR gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + ' 11:00:00'); //PROCESS THE DAYS while( n != 0 && lfs != 0 ){ gdt.addDaysLocalTime(i); if (sched.isInSchedule(gdt)) //defaults to user's time zone n = n + i; else lfs = lfs - 1; } //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 00:00:00"); //RETURN THE DATE return gdt; }

Object:	Script Include
Name:	bdaysAgoEnd
Application:	Global
Accessible from:	All application scopes
Client callable:	TRUE
Description:	Script include for use in query time conditions in order to find & set the end (23:59:59) of n number of "Business Days" ago. A current goal is to return the "business day" in the user's time zone, to provide expanded compatibility for service desks to query based on their service desk's time zone.
Script:	function bdaysAgoEnd(n){ var gdt = new GlideDateTime(); var sched = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828'); //8-5 Excluding Holidays, floating timezone var i = n > 0 ? -1 : 1; //if positive n subtract days, otherwise add var lfs = 170; //Loop Fail Safe: prevents massive loops while allowing up to ~1 year query //CALIBRATE TO A BUSINESS HOUR gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + ' 11:00:00'); //PROCESS THE DAYS while( n != 0 && lfs != 0 ){ gdt.addDaysLocalTime(i); if (sched.isInSchedule(gdt)) //defaults to user's time zone n = n + i; else lfs = lfs - 1; } //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 23:59:59"); //RETURN THE DATE return gdt; }

You call these in the same fashion. But here is a sample query, in case it helps:

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONLast Business Day@javascript:bdaysAgoStart(1)@javascript:bdaysAgoEnd(1)%26sysparm_first_row%3D1%26sysparm_view%3D

Now we have a lot of additional options again.

Last Business Day
Last 5 Business Days
Next 3 Business Days
this list could go on and on...

ENJOY!

It is my hope that you find this article useful. I apologize if this is common knowledge, but I had a hard time finding it personally. This is my personal result of a fair amount of digging & determination. 🙂 Please feel free to use this knowledge and contribute your own. Thanks!

Kind Regards,

Joseph

JosephW1 · ‎06-02-2020

I just updated the business days related script includes. Now they:

Operate on whichever GlideSchedule you pass them
Accept any number of business days ago/from now
can give you a past or future date
do not count holidays as business days
honor the logged in users's timezone
has a better infinite loop fail safe

This turns the script into a multi-tool, giving us a lot more flexibility. It can now give us a lot more than just the Last Business Day, while still being able to give us that when we wish.

JosephW1 · ‎06-03-2020

Now I am trying to figure out how I would implement these on Datasets. The datasets are stored on the Report Layers [sys_report_layers] table, and the conditions are stored in its Filter field.

I don't think we can import a list-view table's query into a dataset, so there goes that option.
According to the Configure list editor properties doc it looks like list-editing is quite locked down on Conditions fields. There goes that option, maybe, unless I can override that.
Any other ideas?

JosephW1 · ‎06-05-2020

There was originally a problem with the scripts I posted regarding the comment above.

I wasn't being careful enough with timezones and it was causing their behavior to shift if the user's timezone is on a different day than the system's timezone. The scripts worked most of the time, but in that scenario yesterday conditions began returning today. Not desired, lol. I've just now updated the bdaysAgo[Start/End] and weeksAgo[Start/End] scripts to honor the user's timezone to give more consistent results.

I also just now added the better loop fail safe. I'm sure a better one could be made, but it was my best attempt.

JosephW1 · ‎06-18-2020

I updated the main article to point out that these can be used with PA Indicators once they have been converted to Report Sources. Cool!

Joe10 · ‎09-17-2020

Hi Joseph,

Just a heads up, there's a typo in the weeksAgoEnd function - you've got gdt as gdt2:

gdt.addDaysLocalTime( 7 - gdt2.getDayOfWeekLocalTime() );

Cheers,

Joe

JosephW1 · ‎09-18-2020

Thanks Joe! I appreciate that, it was an artifact from some of my edits that's not in my PDI's version of the script, and I probably wouldn't have noticed that it was in this post's version. Thanks!

Hey, if you've seen the Business Days Scripts in the main post, take another look at them, please. I noticed some compatibility issues that arose when users changed their date/time format and time zone that I didn't catch during my earlier development.

I've changed the scripts so that they return consistent results regardless of the user's date/time format settings, while honoring the user's time zone.

Drew Carpenter · ‎07-05-2022

I just found this post and although it's old it's still very timely. Dealing with a current customer who wants to see lists based on business days, not calendar days, and in Configurable Workspace.
I wanted to comment here that I'm glad I didn't give up when I read:

"Minor detail, just don't try to save these as filters. Save them as favorites, reports, report sources, or copy & pasted URLs instead. 🙂 )"

Because I'd be dead in the water with workspace. However, you CAN use these as filters, you just have to update the filter record via background script to have the "filter" field populated correctly! (Not only that, I don't know if it has to do with Rome, but I found I had to update all the Report source records this way, too).

On workspace landing page, edit a list's Filter, then from the 'Use existing filter list' choose your new custom Filter.

For a data visualization component, edit its Data source, then in the Edit filter dialog, from the 'Select a predefined condition,' choose your new custom Report source.

**Update the sys_filter record via background script to hold your custom filter**
My script includes are titled busDaysAgoStart and busDaysAgoEnd:

var filterGR = new GlideRecord('sys_filter');
filterGR.get('{sys_id of filter}');

filterGR.setValue('filter', 'opened_atONLast Business Day@javascript:busDaysAgoStart(1)@javascript:busDaysAgoEnd(1)');
filterGR.update();

Tim_Urben · ‎01-10-2023

Could you use this process to query on tickets opened more than x business days after the effective date?

Previously I was just using:

gr.addQuery"opened_atMORETHANu_effective_date@day@after@5"

Ideally submitted date is before or equal to the effective date and that's what we are trying to capture.

@JosephW1

JosephW1 · ‎01-11-2023

@Tim_Urben You could make that filter a few ways. (You can't use the OOTB [MORE/LESS]THAN operators, since they're both unware of business days and not open to edits AFAIK)

With Database Modification (Preferred to avoid a while loop during the query)

1. Add a "Bsn. Days Until Effective" decimal column to your table and populate it with a scheduled job.

2. Add a hint to said field "The business days until effective at the time the record was created" (optional but highly recommended)

3. From your condition builder, add a "Bsn. Days Until Effective > less than or equal to > -5" condition

Another big pro of this method is - assuming this days-until-effective is very ingrained in your organization's processes - this exposes the datapoint to be used in a much more tangible way across the organization. Now people can see it in lists, easily use it in queries, chart by it in reporting, and you can even utilize it in background logic (business rules, scheduled jobs, etc) without the overhead of the below method's while loop.

No Database Modification (Not Preferred due to while loop necessity)

1. Create an openedToEffectiveBusinessDays(days, encodedQuery) script include

2. Make said script query the table and loop the records, comparing their dates and pushing the sys_ids of qualified records into an array

3. Make said script return the array after the while loop

4. From your condition builder, add a "Sys ID > in > javascript&colon;openedToEffectiveBusinessDays(-5, encodedQuery)" condition

Jeffrey Siegel · ‎04-15-2024

@JosephW1

I don't think your weeks ago start/end is taking into consideration customized week starts set in system property 'glide.ui.date_format.first_day_of_week'. I have mine set to sunday, and your script is giving me starting on monday, logically the below should work to get it to see the system property and have the start date adjusted, however i'm having an issue... Running this through a background script to test I get the expected date, however if I run it on a dashboard, i get an error. if I hard code the weekStart variable to 1 (which is commented out below), which is what my system property is, it works fine, but that shouldn't be needed. Via background script, i get the same exact results, and types when i have the variable weekStart set manually to 1, or if it pulls the 1 through the system property. Anyone know why this may be?

function weeksAgoStart(n){
var gdt = new GlideDateTime();
var weekStart = 0;
var weekStart2 = gs.getProperty('glide.ui.date_format.first_day_of_week', "2");
weekStart = parseInt(weekStart2, 10);
//weekStart = 1;  -  IT ONLY WORKS IF THIS LINE IS NOT COMMENTED OUT.  


//SET THE DATE
gdt.addWeeksLocalTime(n * -1);
var weekCalc = (weekStart - 1);
gdt.addDaysLocalTime((gdt.getDayOfWeekLocalTime() - weekCalc) * -1 );

//CORRECT THE TIME
gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 00:00:00");

//RETURN THE DATE

return gdt;
}

Manan Bhatt · ‎07-29-2024

Hey JosephW1,

This is a great article and still very helpful. I was wondering if the script includes automatically adjusted for Day Light saving times or an additional config needs to be made?

Steve McCulloug · ‎09-16-2024

Hi,
This functionality is exactly what I'm looking for, but when I attempt to implement it I always get zero results returned after amending the URL. Is there something I'm missing?

Steve McCulloug · ‎09-16-2024

I figured it it out, when addressing the script includes in the URL I had not omitted the "gs".
Working fine now!

poonam_modi · ‎09-26-2024

Thanks a lot for this article. After exhausting multiple options, I came across this article which helped me get desired results.

Steve McCulloug · ‎12-11-2024

I'm in the process of converting several reports using this trick to the new Platform Analytics app, but you don't seem to be able to create visualizations from the list view the same way you can with current reports.

Any ideas?

JasonE · ‎01-24-2025

This is not working for me. I assume I am doing something wrong.

Here is my script include function. It is client callable.

function subtractDays(numberOfDays) {

    var today = new GlideDateTime();
    var todayString = today.getDisplayValue().split(' ')[0];
    todayString = String(todayString);

    var grFederalHolidays = new GlideRecordSecure('cmn_schedule_span');
    grFederalHolidays.addQuery('schedule', '9b5d9dff93737910c1d8ba027cba10d4');
    grFederalHolidays.orderBy('end_date_time');
    grFederalHolidays.query();

    var holidayDateArray = [];

    while (grFederalHolidays.next()) {
        holidayDateArray.push(grFederalHolidays.end_date_time.getDisplayValue().split(' ')[0]);
    }

    var dayCounter = 0;

    while (dayCounter < numberOfDays) {

        if (holidayDateArray.includes(todayString) || today.getDayOfWeek() > 5) {

            today.addDaysUTC(-1);
            todayString = today.getDisplayValue().split(' ')[0];
            todayString = String(todayString);

        } else {

            today.addDaysUTC(-1);
            todayString = today.getDisplayValue().split(' ')[0];
            todayString = String(todayString);

            dayCounter++;
        }
    }
    today.setDisplayValueInternal(today.getDisplayValueInternal().split(' ')[0] + '00:00:00');

    return today;
}

Here are some URLs I tried

nav/ui/classic/params/target/sc_task_list.do%3Fsysparm_query%3Dsys_updated_on%253C5%2520business%2520days%2520ago%40javascript%3AsubtractDays(5)%26sysparm_first_row%3D1%26sysparm_view%3D

nav/ui/classic/params/target/sc_task_list.do%3Fsysparm_query%3Dsys_updated_on%253C%40javascript%3AsubtractDays(5)%26sysparm_first_row%3D1%26sysparm_view%3D

I really wish there was a better way to do buiness day comparison or to allow scripted filters on date fields.

Thanks in advance.

hcallen · ‎07-30-2025

@JasonE

"Sandbox enabled" may need to be enabled on the script include. It didn't work for me until I did such.