CAML Query filter by date range is not working in SharePoint 2013 JavaScript client object model

In this SharePoint CAML query filter tutorial, we will discuss how we can use CAML based on created date range in SharePoint 2013/2016/Online. How to use caml query between two dates in SharePoint Online/2013.

CAML Query filter by date range

Recently I was working on reporting using the JavaScript client object model in SharePoint 2013. Here my requirement is to retrieve records from a SharePoint 2013 list between created date. Let’s say I want to retrieve records created between 1st Jan to 30th Apr 2015.

So I have written the query in the below format where startDate is the created start date and endDate is the created end date. And also I have included IncludeTimeValue=’FALSE’, so that it will not consider the time part in the created date.

var startDate="2015-01-01T00:00:01Z";
var endDate="2015-30-04T00:00:01Z";

query = "<View Scope='RecursiveAll'><Query><Where><And><Geq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>"+startDate+ "</Value></Geq><Leq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>"+ endDate + "</Value></Leq></And></Where></Query></View>";

When I run this query, it did not give me the result. After doing some research, I found that I gave the incorrect date format.

I had given the date format as “yyyy-dd-mmT00:00:01Z“. And it was the reason the filter was not working. When I change the format to “yyyy-mm-ddT00:00:01Z” the filter started working. So my startDate and endDate will be like below:

var startDate="2015-01-01T00:00:01Z";
var endDate="2015-04-30T00:00:01Z";

This is a very silly issue, but just want to share because if you ever face this kind of issue you may check the date format. Will surely save your time.

You may like following SharePoint CAML Query examples:

I hope this SharePoint 2013 caml query example helps you to filter records between two days in SharePoint Online/2013/2016.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.