CAML Query Filtering with OffsetDays

Question

I'd like to set up a CAML query in a Data View Web Part (DVWP) that works like this:

• If a date is not specified on the Query String, then show me items created in the last 30 days
• If a date is specified on the Query String, then show me items created from that date back 30 days

The first one is pretty easy:

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

Offset Days seems to only be available with Today, though.

Any ideas?

Answer 1

What about approaching it from the other end of the solution? If users are clicking a link of some kind to get to this view (is it a data view web part?), you could use JavaScript (which can handle date offsets relatively easily) to append a query string with the start date and end date of the range you need, and use both of those parameters in your CAML query.

Or, if the date information is coming from a Date/Time column in a list, you could add a calculated column which can also handle date offsets relatively easily.

Answer 2

You're probably wanting to do this client side right Marc?

The only thing that comes to my mind in that case is to grab the data in the query string and do your calculations in jQuery (assuming you're using your SPService library with this) and pass the hard dates into your CamlQuery as a beginning and end points and not do offsets.

Answer 3

I'm entering this discussion a bit late, but for reference you can use a single web part to filter by dates with a default date range.

The key is that you can pass a either a value or a CAML fragment as a parameter in a DVWP

The solution is:

• Create a calculated column "CreatedPlus30"
  • Since we can't subtract from the querystring parameter, we must add to the created field instead
• Create a querystring parameter in the DVWP which will filter on the date field.
• Set the default value of the parameter to the Today CAML variable. <ParameterBinding Name="DateFrom" Location="QueryString(DateFrom)" DefaultValue="&lt;Today/&gt;"/>

The data will then be filtered by the querystring parameter only if it exists, otherwise by the current date (adjusted for your offset)

Detail and code samples for your question above:

Create a calculated column "CreatedPlus30" - Since we can't subtract from the querystring parameter, we must add to the created field instead

To get items created in the last 30 days you would use the following CAML fragment: <Geq><FieldRef Name="Created"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq>

We will offset by 30 days: <Geq><FieldRef Name="CreatedPlus30"/><Value Type="DateTime"><Today/></Value></Geq>

You can also use this field to compare against a date value with the following CAML fragment: <Geq><FieldRef Name="CreatedPlus30"/><Value Type="DateTime">yyyy-MM-ddTHH:mm:ssZ</Value></Geq>

We now have 2 queries filtering on the same field, where the "value" portion of the query is yyyy-MM-ddTHH:mm:ssZ or <Today/>

Encoding the CAML fragment so we can use in the DVWP data source select command, this is our final parameter: <ParameterBinding Name="DateFrom" Location="QueryString(DateFrom)" DefaultValue="&lt;Today/&gt;"/>

This is our final parameterized CAML fragment: <Geq><FieldRef Name="CreatedPlus30"/><Value Type="DateTime">{DateFrom}</Value></Geq>

Encoded for the DVWP data source query: <Geq><FieldRef Name="Created"/><Value Type="DateTime">{DateFrom}</Value></Geq>

Answer 4

DateTime endDate = Request.QueryString("StartDate") ?? DateTime.Now;//Edit as needed
DateTime startDate = endDate.AddDays(-30);

string query = string.Format(
@"<Where>
  <And>
    <Geq>
      <FieldRef Name='Created'/>
      <Value Type='DateTime'>
      {0}
    </Value>
    </Geq>
    <Leq>
      <FieldRef Name='Created'/>
      <Value Type='DateTime'>
      {1}
    </Value>
    </Leq>
  </And>
</Where>
", 
SPUtility.CreateISO8601DateTimeFromSystemDateTime(startDate), 
SPUtility.CreateISO8601DateTimeFromSystemDateTime(endDate));

Note that this doesn't just handle the second case, it handles both cases.

Answer 5

Syntax and a working solution Works in SP 2013.

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