Comparing dates in Sharepoint 2010 CAML query

Question

I'm running into an interesting problem when trying to query a Sharepoint list.

Here is the query so far:

camlQuery.ViewXml = new XElement("View",
    new XElement("Query",
        new XElement("Where",
            new XElement("And",
                new XElement("Geq",
                    new XElement("FieldRef", new XAttribute("Name", "Created_x0020_Date")),
                    new XElement("Value", new XAttribute("IncludeTimeValue", "TRUE"), new XAttribute("Type", "DateTime"), InStartDate.Get(context))),
                new XElement("Leq",
                    new XElement("FieldRef", new XAttribute("Name", "Created_x0020_Date")),
                        new XElement("Value", new XAttribute("IncludeTimeValue", "TRUE"), new XAttribute("Type", "DateTime"), InEndDate.Get(context))))))).ToString();

The Created_x0020_Date column is just when the row was created.

What's interesting is that, no matter what I make the start and end dates, the query errors out with "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." The error seems to think that I'm returning more rows than the threshold is prepared to handle. But this error occurs even when my end date is before my start date, which should mean that it's impossible to find a date that matches the where clause.

Let me know if I can provide any more information.

Why create your own "Created Date" field when SharePoint has its own "Created" field that it auto-populates for you? — Servy
That's why I posted it as a comment, not an answer. It's not an answer to your question, it's just a way of saving you the effort of creating and maintaining a field that SharePoint is already creating and maintaining for you. — Servy

Servy Servy · Accepted Answer · 2014-06-11T16:56:37

This has nothing whatsoever to do with dates, or how they are compared. You'd see this issue with just about any type of filtering that you try to perform on this list.

This is because the List View Threshold isn't about the size of the result set.

The LVT is triggered any time any intermediate result set is larger than the threshold. Your query requires searching all [size of your list] items in order to find out which items are less than/greater than the dates that you're searching on. The fact that the result set is smaller than the LVT doesn't matter; SharePoint gives up on you long before then.

Of course, if your "Created Date" field had an index on it then it wouldn't need to search [size of list] items to figure out all of the dates within that range. It'd be able to use the index to get right at just those items, so in that case, as long as your result set is less than the LVT you're fine.

So as for actual solutions, you can:

Add an index to this column. It may or may not be sufficient for your purposes as you may or may not be able to ensure that every query can use an indexed column to filter the list to less than the LVT every time.
Increase the LVT to greater than the size of the list.
Disable the LVT for this list.
Override the LVT for this query (which can be done when performing the queries in code)
Ensure the user is an admin user (possibly privilege escalating) as they have a higher LVT.
Make the list smaller, so that it is below the LVT.
- A variation on this is putting the items into folders in which each folder has less than the LVT. You can then do queries on each folder without issue (because an items folder is an indexed column).
Pull down the entire list into memory (in pages less than the LVT) and perform your query on in-memory objects.

Comparing dates in Sharepoint 2010 CAML query

1 Answers