A CAML Query Quick Reference

I’ve been doing a lot of work with SPServices and SharePoint lists lately, and I find myself using the same CAML queries over and over. Unfortunately I don’t always remember how to format some of the more common queries, so I decided to make a quick reference. There are already some good tools and resources out […]

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.

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

(more…)