Using a Current Date Offset in a CAML Query to Filter List Items and Other Useful CAML Resources

Recently I needed to create a Data View Web Part in MOSS 2007 that displayed all upcoming events, as well as all events that took place within the last week. The out of the box filtering options in SharePoint Designer (SPD) let you filter a Date and Time column by the “[Current Date]” or by a specific date. Unfortunately the UI does not let you specify an offset for the “[Current Date]” filter, but I found a few resources that showed me how to modify the CAML query that creates the filter to accomplish a date offset.

Setting Up the Filter

I set up my filter (Common Data View Tasks > Filter) to show all items whose Start Time (aka @EventDate) is greater than or equal to “[Current Date]” to build the initial CAML query.

<DataSources>
	<SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Geq&gt;&lt;FieldRef Name=&quot;EventDate&quot;/&gt;&lt;Value Type=&quot;DateTime&quot;&gt;&lt;Today/&gt;&lt;/Value&gt;&lt;/Geq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" UseInternalName="true" ID="dataformwebpart2">
	<SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="537F0267-8B1E-47CD-8FC7-356D4949F604"/></SelectParameters>
	<UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="537F0267-8B1E-47CD-8FC7-356D4949F604"/></UpdateParameters>
	<InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="537F0267-8B1E-47CD-8FC7-356D4949F604"/></InsertParameters>
	<DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="537F0267-8B1E-47CD-8FC7-356D4949F604"/></DeleteParameters>
	</SharePoint:SPDataSource>
</DataSources>

Find the selectcommand attribute; everything inside the quotes for this attribute is the actual CAML query. It’s escaped by default, so it will look like this:

<View><Query><Where><Geq><FieldRef Name="EventDate"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq></Where></Query></View>

I find it much easier to un-escape the CAML before trying to modify it, so I used http://htmlentities.net to decode the query. Then I modified the <Today/> element by adding an “OffsetDays” attribute with a value of “-7” like this: <Today OffsetDays="-7"/>. I came across some information that said you could simply use “Offset” instead of “OffsetDays,” but that didn’t work for me. The re-escaped CAML should look like this:

<View><Query><Where><Geq><FieldRef Name="EventDate"/><Value Type="DateTime"><Today OffsetDays="-7"/></Value></Geq></Where></Query></View>

I pasted the new CAML into the SelectCommand attribute to replace the CAML that SPD generated. The resulting CAML query filters out all items that have a Start Time prior to one week ago as of the day the page is viewed. Here is a screenshot of the resulting DVWP as viewed on April 5, 2011. Notice the default Calendar web part above only shows the event that occurs in the future.

This is a pretty simple modification to filter items based on a certain number of days in the past or future (use a positive number instead of a negative number to filter by a particular number of days in the future). You could even add a second filter for less than or equal to “[Current Date]”, then set that offset in the future to have a rolling “window” for items to show up. Here’s a sample that shows all events from 30 days ago to 30 days in the future (un-escaped and escaped):

<View><Query><Where><And><Geq><FieldRef Name="EventDate"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq><Leq><FieldRef Name="EventDate"/><Value Type="DateTime"><Today OffsetDays="30"/></Value></Leq></And></Where></Query></View>

<View><Query><Where><And><Geq><FieldRef Name="EventDate"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq><Leq><FieldRef Name="EventDate"/><Value Type="DateTime"><Today OffsetDays="30"/></Value></Leq></And></Where></Query></View>

Warning: As soon as you use the Filter menu in the Common Data View Tasks menu to modify or add a new filter, your offsets will be removed by SPD. You’ll need to modify the CAML query and add the offsets again.

Useful CAML Resources

• SharePoint Magazine – Writing CAML Queries for Retrieving Items from a SharePoint List – This article is the reason I understand CAML queries. It explains how they work and how to construct your own simple or complex queries.
• Share The Learning – Querying in CAML Relative to Today’s Date – This is the first blog post I found that said to use “OffsetDays” instead of just “Offset” in the CAML.
• How to: Customize the Content By Query Web Part by Using Custom Properties – This article is referenced in the blog post from the previous link.
• Patrick Tisseghem’s Blog [MVP SharePoint] – Using Today in a CAML Query – This post has some more information on date offsets using CAML in SPD and writing code.

If anyone has some other CAML resources, let me know in the comments!