How to add a literal static date value in a date interactive filter?

[Todd O]

I'm wanting to add a literal static date value as a selectable option for an interactive date filter. To do this, there is a business rule called "Get Date Filter Options for Date Filters" where you are supposed to just add your entry as another line in there.  I can add simple ones using relative date references and those are easy, and they work great. But, I'm wanting to add a static date value (e.g., '2022-01-01') and none of the variations I've tried are working. Below are just a few of the basic ones I've tried. 

answer.add('180_Base year@javascript:new GlideDateTime("'+new RequestUtils().getBeginningDate()+'")@javascript:new GlideDateTime("'+new RequestUtils().getEndingDate()+'")', gs.getMessage('Base year'));

answer.add("180_Base year@javascript:gs.dateGenerate('2021-01-01','00:00:00')@javascript:gs.dateGenerate('2022-12-31','23:59:59')", gs.getMessage('Base year'));

There is a KB on this topic at this location. https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0749822

In this KB it is stated that relative dates can be used and they provide many examples. They also state that literal static values can be used but, unfortunately no examples are provided. Here is a snippet of that paragraph from that KB.

Below are additional community posts that I have already tried and none of them provide an answer nor are marked as answered. Hopefully this avoids someone replying with one of these links saying this may help…(unless of course there is a discussion that I missed and provides an answer).

1. https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0749822
2. https://www.servicenow.com/community/platform-analytics-forum/what-is-the-syntax-to-add-custom-date-...
3. https://www.servicenow.com/community/developer-forum/script-include-for-date-filter/m-p/1592995
4. https://www.servicenow.com/community/developer-forum/add-year-choices-to-quot-get-date-filter-option...

Additionally, I’ve added the “Debug homepage filters” onto my dashboard to be able to see the filter being applied. I’ve noticed that for all the date filters applied, it always uses the “ON” filter and it appears that is hard coded. I would like a “BETWEEN” version for my use case. Is there anyway to make this a “BETWEEN?” No matter what I put in the “Get Date Filter Options for Date Filters” business rule, this filter is always “ON” value. See image below.

 Any help is greatly appreciated!

[Todd O]

Hi @Jiri K, the issue may be caused by the default date format set for your instance. Since we are hard coding these dates in the business rule, I believe the date format in the BR must match the format of the system property for default date format. Here's what you can do.

1. Open up system property called glide.sys.date_format.  Take note of the date format.
2. Update the business rule custom entries you are adding to be in the same format as the system property on your instance. 
3. If you're using the gs.getMessage functions, make sure that you've added entries in the message table (sys_ui_message). 
4. Lastly, if you want to debug your interactive filter on the dashboard, you can add the debug widget temporarily to see how the filter is getting applied.  See last image I pasted below showing this. 

I've also pasted below my working version along with my system property date format. Hopefully this helps you. You can change the order that shows up in the drop down by reordering the items in the business rule. 

Debug your interactive filters by adding this temporarily to your dashboard. 

[Jiri K]

Hello @Todd O thank you for the quick response. 

I got it working with the yyyy/mm/dd  time format as thats what our instance was set to. So filtering only for the month of December 2023 looks like this:

answer.add('DEC_Year 2023Dec@2023/12/01 00:00:00@2023/12/31 23:59:59', gs.getMessage('Full Year 2023'));

Good to know its possible change the order that filters appear up in the drop down by reordering the items in the business rule. 

Again, thank you so much for this!

[Badamski]

I'm having the same issue you mentioned with it always adding "ON" to the query which seems to always break it. I'm trying to create entries in this business rule based on the date ranges of fiscal quarters and years. I'm focused on creating a test option now for January 2024 and have tried the syntax a few different ways but none of them seem to work and always show broken filters in the debug log, I do have MM/dd/yyyy date formatting in my instance and the sys_ui_messages created and here's some of the formatting I've tried.

answer.add('004_FY24@javascript:gs.dateGenerate("2024-01-01","00:00:00")@javascript:gs.dateGenerate("2024-12-31","23:59:59")', gs.getMessage('FY2024'));
answer.add('002_FY24@01/01/2024 00:00:00@01/31/24 23.59:59', gs.getMessage('FY2024'));
answer.add('002_FY24TEST@javscript:gs.dateGenerate("01/01/2024","00:00:00")@javscript:gs.dateGenerate("01/31/24", "23.59:59")', gs.getMessage('FY2024TEST'));
answer.add('003_FY24@javscript:gs.dateGenerate("2024-01-01","00:00:00")@javscript:gs.dateGenerate("2024-01-31", "23.59:59")', gs.getMessage('FY2024'));

I'm also curios as to why despite having the same date formatting as you, your example below consistently doesn't work in my instance. I have a test dashboard for incident that I'm testing this against for now but I can't seem to get a custom interactive date filter working like the ootb date filter but with fiscal periods. 

answer.add('205_Year2023@01/01/2023 00:00:00@12/31/2023 23:59:59', gs.getMessage('Year 2023'));