Query EndDate is being ignored

[Bruce Scarborou]

It appears that the end-date specified in the URL sent to the API to return records for a specific date or date range is being ignored and I get results that include records which were created AFTER the end date - the field I am querying on is sys_updated_on.

Here is an example where I am trying to pull older records, from July 1 2021 only, via the SN REST API, but it is returning all results starting from July 1, 2021 up to today.

This is the URL that is being sent to the API:

https://XXX.service-now.com/api/now/v1/table/incident?&sysparm_query=sys_updated_on>=2021-07-01 00:00:00&sys_updated_on<2021-07-02 00:00:00&sysparm_limit=5000&sysparm_offset=0&sysparm_orderby=sys_updated_on

Here's a similar URL, that is supposed to just return records updated yesterday, but the results include records from today as well:

https://XXX.service-now.com/api/now/v1/table/incident?&sysparm_query=sys_updated_on>=2022-06-13 00:00:00&sys_updated_on<2022-06-14 00:00:00&sysparm_limit=5000&sysparm_offset=0&sysparm_orderby=sys_updated_on

Both URLs specify that the results should have sys_updated_on LESS THAN the end date, but I am getting results after those dates.

Am I missing something?

BTW, I am new to this and am not sure which topics are appropriate, so if this should have different topics, please advise.

Thanks

Bruce

[Allen Andreas · Accepted solution]

Hi,

The best way to do this is to navigate to the list view for those records. Build the filter conditions there, then right-click the last piece of the bread-crumb and choose "copy query" and then use that. This will ensure proper formatting, etc.

sys_updated_on>=javascript:gs.dateGenerate('2021-07-01','00:00:00')^sys_updated_on<javascript:gs.dateGenerate('2021-07-02','00:00:00')

which then translates to:

sysparm_query=sys_updated_on%3E%3Djavascript%3Ags.dateGenerate('2021-07-01'%2C'00%3A00%3A00')%5Esys_updated_on%3Cjavascript%3Ags.dateGenerate('2021-07-02'%2C'00%3A00%3A00')

when used in REST API Explorer

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Bruce Scarborou]

Thanks Allen.

I had found some examples similar to what you posted, but using "between" and here's what worked for me:

https://sbgi.service-now.com/api/now/v1/table/incident?&sysparm_query=sys_updated_onBETWEENjavascript:gs.dateGenerate('2022-06-13','00:00:00')@javascript:gs.dateGenerate('2022-06-13','23:59:59')&sysparm_limit=5000&sysparm_offset=0&sysparm_orderby=sys_updated_on

BTW, I have tried to mark your reply as both Helpful and Correct Answer, but for some reason neither has taken effect - yet. Will try again

Thanks again