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

Web Design & Development

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.

[Current Date] filter
Set the DVWP to filter items based on the current date.
Next I switched to split view and found the CAML query in the code. It’s located in the <SharePoint:SPDataSource> tag (in the <DataSources> section):

<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:

&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 OffsetDays=&quot;-30&quot;/&gt;&lt;/Value&gt;&lt;/Geq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;

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:

&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 OffsetDays=&quot;-7&quot;/&gt;&lt;/Value&gt;&lt;/Geq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;

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.

Resulting DVWP with offset days filter
The DVWP now displays items with a Start Time greater than or equal to 7 days in the past.

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>
&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;And&gt;&lt;Geq&gt;&lt;FieldRef Name=&quot;EventDate&quot;/&gt;&lt;Value Type=&quot;DateTime&quot;&gt;&lt;Today OffsetDays=&quot;-30&quot;/&gt;&lt;/Value&gt;&lt;/Geq&gt;&lt;Leq&gt;&lt;FieldRef Name=&quot;EventDate&quot;/&gt;&lt;Value Type=&quot;DateTime&quot;&gt;&lt;Today OffsetDays=&quot;30&quot;/&gt;&lt;/Value&gt;&lt;/Leq&gt;&lt;/And&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;

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

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


Comments

  1. [...] some of the more common queries, so I decided to make a quick reference.There are already some good tools and resources out there for building CAML queries. U2U’s CAML Query Builder is usually what I fall back to [...]

Comments are closed