Query datatime using WebAPI
Fetching data based on date field using WebAPI
Category: Dynamics 365,Dynamics 365 CE datetime filter for WebAPI,Query datatime using WebAPI,Query Dynamics 365 CE using WebAPI,Query entity record based on datetime field,WebAPI datetime filter for Dynamics 365 CE Author: HIMBAP Date: 8 months ago Comments: 0

Introduction
Are you looking for sample code to fetch data from Dynamics 365 CE using WebAPI ? if yes this post will help. We are discussing how to fetch data based on the datetime field from account entity.

Look for our other WebAPI Samples here

Requirement
Retrieve accounts records which created on or before today’s date.

Solution
Write writing WebAPI request first thing make sure to check all the available filters that we can use in our WebAPI request, you can get full details of supported filters here.

Now our requirement is to compare data based on the current date, so first thing we need to get current data and format it correctly using libraries like Moment.js or you can write your own javascript code to format like following.

function FormatDate() {
    var today = new Date();
    var month = today.getMonth() + 1; //to use correct month, it will return 0 for jan
    var day = today.getDate();
    var year = today.getFullYear();
    return month + "-" + day + "-" + year;
}

Above function will get current date and will format it to “mm-dd-yyyy”, what we need to query Dynamics 365 CE. Now we can use it in our WebAPI request query. In following example I am going to query account entity record which is created today or before. I am counting total number of records, to count number of records we can use count option in our query.

function retrieveAccounts() {
   
    var currentDate = FormatDate();
    var entity = "accounts";
    var columnSet = "?$select=name&$filter=createdon le '" + currentDate + "'&$count=true";
    var serverURL = Xrm.Page.context.getClientUrl();
    var Query = entity + columnSet;
    var req = new XMLHttpRequest();
    req.open("GET", serverURL + "/api/data/v9.0/" + Query, true);
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
  
    req.onreadystatechange = function() {
        if (this.readyState == 4 /* complete */ ) {
            req.onreadystatechange = null;
            if (this.status == 200) {
                var data = JSON.parse(this.response);
                if (data['@odata.count'] != null)
                    alert("Total Records:"+ data['@odata.count']); 
            } else {
                var error = JSON.parse(this.response).error;
                alert(error.message);
            }
        }
    };
    req.send();
}

Summary
We learned how to format date using java script code and learned how to use formatted date in WebAPI request. You can use above code in your web resource and can do required changes.

Hope it will help someone !!

Source: Mahendra Pal