Query items from list between Time ranges

Question

I am trying to view all items on time between 08:00 to 17:00, I get only one item count even if I have more?

var fromTime = new Date(result);
            fromTime.setHours(8);
            var toTime = new Date(result2);
            toTime.setHours(17);
            var camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml('<View><Query><Where><And><Geq><FieldRef Name=\'EventDate\'><Value IncludeTimeValue=\'TRUE\' Type=\'DateTime\'>' + fromTime.toISOString() + '</Value><FieldRef Name=\'EndDate\' /></Geq><Leq><Value IncludeTimeValue=\'TRUE\' Type=\'DateTime\'>' + toTime.toISOString() + '</Value></Leq></And></Where></Query></View>');


            this.collListItem = list.getItems(camlQuery);
            context.load(collListItem);

            var itemCount = collListItem.get_count();
            if (itemCount > 0) {
                alert(itemCount);
            }

item count gets only 1 when i have 2 items in the list why?

Answer 1

sorry, did not understand your question and noticed your code, you want to pick up items filtering EventDate and EndDate?

What is the value of fromTime.setTime() and toTime.setHours(17)?

trying to answer your question:

the format example: date to filter between 2014-12-02T08:00:00Z and 2014-12-02T17:00:00Z

camlQuery.set_viewXml("<View><Query><Where><And><Geq><FieldRef Name='date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2014-12-02T08:00:00Z</Value></Geq><Leq><FieldRef Name='date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2014-12-02T17:00:00Z</Value></Leq></And></Where></Query></View>");

before using this code checks FieldRef Name = 'date'.

I had search and test setHours(8 or 17) not work perfectly with toISOString() see the documentation here, Unfortunate not is possible, but i have logic for resolve this question.

Update(2):

var fromTime = new Date(result);
var fromtimeConverted = fromTime.toJSON().split('T')[0] + "T08:00:00Z";  //fromTime.setHours(8);
var toTime = new Date(result2);
var toTimeConverted = toTime.toJSON().split('T')[0] + "T17:00:00Z";//toTime.setHours(17);
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml('<View><Query><Where><And><Geq><FieldRef Name=\'EventDate\'><Value IncludeTimeValue=\'TRUE\' Type=\'DateTime\'>' + fromtimeConverted + '</Value><FieldRef Name=\'EndDate\' /></Geq><Leq><Value IncludeTimeValue=\'TRUE\' Type=\'DateTime\'>' + toTimeConverted + '</Value></Leq></And></Where></Query></View>');


this.collListItem = list.getItems(camlQuery);
context.load(collListItem);

var itemCount = collListItem.get_count();
if (itemCount > 0) {
    alert(itemCount);
}

Answer 2

Your query is wrong. Try this

'<View><Query><Where><And><Gt><FieldRef Name=\'EventDate\' /><Value IncludeTimeValue=\'TRUE\' Type=\'DateTime\'>' + fromTime.setTime() + '</Value><Gt><Lt><FieldRef Name=\'EndDate\' /><Value IncludeTimeValue=\'TRUE\' Type=\'DateTime\'>' + toTime.setHours(17) + '</Value></Lt></And></Where></Query></View>'

Answer 3

I'm sure your query is wrong, you are missing the where and query tag into it check this documentation: Retrieve List Items Using JavaScript