REST API Date Range Search

samj13 · ‎04-22-2015

Hello,

I am trying to perform a search on change requests and tasks based on a date range via the REST API. I understand how to query for specific parameters, such as:

/api/now/v1/table/task_ci?sysparm_query=sys_updated_on=2014-10-27+00:00:00

This will return all records in the task_ci table that have a sys_updated_on value equal to that instant in time. However, I am looking to get all records for a certain day. Often times get parameters will have a _lt and _gt addition that allows such functionality, so for example I want to perform:

/api/now/v1/table/task_ci?sysparm_query=sys_updated_on_gt=2014-10-27+00:00:00&sys_updated_on_lt=2014-10-28+00:00:00

Anyone have an idea on how to query for date ranges on records via the REST API?

Thanks.

tony_barratt · ‎04-22-2015

Hi Samuel,

http://wiki.servicenow.com/index.php?title=Table_API

..

4 Methods

4.1 GET /api/now/v1/table/(tableName)

This method retrieves multiple records for the specified table with proper pagination information.

Supported Parameters

Parameter Description

sysparm_query

An encoded query.

For example: (sysparm_query=active=true)(sysparm_query=caller_id=javascript:gs.getUserID()^active=true)

Note: The encoded query provides support for order by. To sort responses based on certain fields, use the ORDERBY and ORDERBYDESC clauses in sysparm_query. For example, sysparm_query=active=true^ORDERBYnumber^ORDERBYDESCcategory filters all active records and orders the results in ascending order by number first, and then in descending order by category.

So you can use a encoded query copied from a list view, like so (untested code):

/api/now/v1/table/task_ci?sys_parrn_query=active=true^sys_created_onBETWEENjavascript:gs.dateGenerate('2015-04-16','00:10:00')@javascript:gs.dateGenerate('2015-04-22','12:59:59')

Best Regards

Tony

View solution in original post

Gheerish129 · ‎05-10-2021

Note that instead of the block: javascript:gs.dateGenerate('2015-04-16','00:10:00')

You can write: 2015-04-16T00:10:00Z
DateTime format: yyyy-MM-ddTHH:mm:ssZ

This also help resolve local time issues (time offset du to time zones)