SharePoint

CAML queries with dates

Recently I was working on a utility that would query a MOSS list for items with update_timestamp field within a certain range. I used CAML queries for the task and this is when I found out that for dates in CAML  query filter the time part gets omitted, i.e. following two CAML queries will return the same results even though time part of the date is different:

<Query>
    <Where>
        <Geq>
            <FieldRef Name='Update_Timestamp' />
                <Value Type='DateTime'>2008-07-14T00:00:00Z</Value>
        </Geq>
    </Where>
</Query>

-----  and ----

<Query>
    <Where>
        <Geq>
            <FieldRef Name='Update_Timestamp' />
                <Value Type='DateTime'>2008-07-14T08:31:00Z</Value>
        </Geq>
    </Where>
</Query>

After some googling I found that the solution for this problem is to add "IncludeTimeValue" argument:

<Value Type='DateTime' IncludeTimeValue='TRUE' >2008-07-14T08:31:00Z</Value> 

Another problem I have been facing is how to include a dynamic date filter that is evaluated at runtime instead of hardcoding the dates, e.g. I want to include items with update_timestamp 5 days back or newer, something like Today() - 5. As I discovered, there is a CAML operator that does just that:

Instead of specifying a date you can do following:

And a closing tip: to create a sharepoint-formatted date string from a DateTime object you can use SPUtility.CreateISO8601DateTimeFromSystemDateTime() method.