Need to fetch data from Table API between the date range

[kishnpriya]

We are running DLP on this API for journal table:-

https://ven04226.service-now.com/api/now/table/sys_journal_field

Here in this API we can filter the data by passing sys param_query, for filtering out the data between the dates we are seeing a limitation from the API. 

https://developer.servicenow.com/dev.do#!/reference/api/tokyo/rest/c_TableAPI

These are the only operators which we can use to filter the data with the parameters

Syntax: sys param query=<col_name><operator><value>.

• <col_name>: Name of the table column to filter against.
• <operator>: Supports the following values:
  • =: Exactly matches <value>.
  • !=: Does not match <value>.
  • ^: Logically AND multiple query statements.
  • ^OR: Logically OR multiple query statements.
  • LIKE: <col_name> contains the specified string. Only works for <col_name> fields whose data type is string.
  • STARTS WITH: <col_name> starts with the specified string. Only works for <col_name> fields whose data type is string.
  • ENDS WITH: <col_name> ends with the specified string. Only works for <col_name> fields whose data type is string.<value>: Value to match against.

We don't have "between" operator or any alternatives to find the range of records between the dates.

Currently we are running scan by passing sys_param_query as "sys updated on BETWEEN javascript&colon ; . dateGenerate ('2023-04-06','13:44:31')@javascript&colon; . dateGenerate('2023-04-13','10:19:41')"

The API is returning all the records even if we pass any wrong value in it.

Is there any way to fetch the records between the dates?

[Ratnakar7]

Hi @kishnpriya ,

Unfortunately, as you mentioned, the Table API in ServiceNow does not support the "between" operator for filtering records based on a range of dates. However, there are a couple of workarounds you could try:

1. Use the "greater than or equal to" and "less than or equal to" operators: Instead of trying to use the "between" operator, you could use the "greater than or equal to" and "less than or equal to" operators to filter records that fall within a date range. For example, you could use a query like this:

sys_updated_on>=2023-04-06^sys_updated_on<=2023-04-13

This would return all records where the sys_updated_on field is between April 6, 2023 and April 13, 2023.

1. Use a Scripted REST API: Another option would be to create a Scripted REST API in ServiceNow that uses server-side JavaScript to filter records based on a range of dates. You could then use this API to retrieve the data you need. This would give you more flexibility in terms of filtering and querying the data, but it would require more development effort than using the Table API.

If my response was helpful in resolving the issue, please consider accepting it as a solution by clicking on the ✅Accept solution button and giving it a thumbs up 👍. This will benefit others who may have a similar question in the future.

Thank you!

Ratnakar