Using FetchXml/Web API to Query Data Between Timestamps(Minute part) in CDS

When working with data validation or some special cases we would need to query data between timestamps. Although we can use Advanced Find to build the query it doesn't allow us to provide the minute part in the query.

This blog shows how you could accomplish this easily using both FetchXML and Web API.

For the sake of simplicity let's look at an example. I have created three contacts as shown below. Let's use 'Created On' as the Date Time field to query the data.

Now if we were to filter the contacts created on between 12.10 PM and 12.20 PM we could do as below

Using FetchXML

• Let's build the FetchXML as we normally would do in Advanced Find

• Download the FetchXML and modify it as shown below. Here we will be using the ISO 8601 format in local time. gt is Greater Than and le indicates Less Than or Equal To.

• If you would like to query the data using UTC format you could specify it using YYYY-MM-DDThh:mm:ssZ the Z suffix to indicate UTC. For example 2020- 08-23T17:10:00Z in the above example
• At this point our FetchXML is ready. Let's verify if the FetchXML is working, I am using FXB utility in the XrmToolBox for this.
• Connect to your org, open the downloaded FetchXML and hit Execute. In our example it will show the below results as expected

Note: I changed the appearance at the bottom to show the results in Local Time

Using Web API Query

If you followed the above steps for FetchXML  than it's very easy to get the Web API query using the FXB.

• Click the OData 4.0 (Web API) and you should the Web API query being generated

Notice that the time has been change to use the UTC format as that's the desired format for Web API. If we take away the Z suffix we will see the below error.

• If you don't want to rely on an external tool and want to quickly generate the OData filter you can use the below syntax for the OData filter

$filter=datefield gt YYYY-MM-DDThh:mm:ssZ and datefield le YYYY-MM-DDThh:mm:ssZ

e.g. $filter=createdon gt 2020-08-23T17:10:00Z and createdon le 2020-08-23T17:20:00Z

You can review the CDS standard filter operators here for additional information.

Hope you found this useful, good day!