CAML Query to Include Time Value in DateTime comparisions

May, 20 2011

1 min/s

The Collaborative Application Markup Language (CAML) query is an XML based language used for filtering and/or sorting data from a list within a SharePoint portal . This is often used in custom webparts and controls while retrieving data.

Let's say, we need to read all the items from a list between some specified dates. We end up preparing a CAML query which looks like

<Query>
   <Where>
      <And>
         <Gt>
            <FieldRef Name='Modified' />
            <Value Type='DateTime'>2011-02-09T12:00:00Z</Value>
         </Gt>
         <Lt>
            <FieldRef Name='Modified' />
            <Value Type='DateTime'>2011-02-10T10:00:00Z</Value>
         </Lt>
      </And>
   </Where>
</Query>

On seeing this query, we understand that, it will fetch all the items from the list which are modified between Feb-09^th 12:00 and Feb-10^th 10:00. But when we exceute this query, no results are returned (even though items are modified between the specified dates). This happens because, by default the timestamp is not considered while comparing the dates. So, in this case both the comparision operators (<Gt> & <Lt>) will always fail, and hence no results are returned for this query.

In such scenarios, we need to use the IncludeTimeValue attribute for the Value element. Add this attribute and assign it true, as shown in the below XML

<Query>
   <Where>
      <And>
         <Gt>
            <FieldRef Name='Modified' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>2011-02-09T12:00:00Z</Value>
         </Gt>
         <Lt>
            <FieldRef Name='Modified' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>2011-02-10T10:00:00Z</Value>
         </Lt>
      </And>
   </Where>
</Query>

Now by adding the IncludeTimeValue attribute, the timestamp is also considered while doing the comparisions. With this query all the list items modified between Feb-09^th 12:00 and Feb-10^th 10:00 are returned in the result set.