Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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.

share|improve this answer
    
I think I'll probably end up doing it this way, but it would be nice to have one page do both things. –  Marc D Anderson Jun 8 '12 at 15:03
    
Can you use just one page and ALWAYS pass the start date and end date, even if it's for today -30? For the calculated column example, you could conditionally calculate today -30 if the original date column is empty, or calculate the date offset if it's not empty. –  Josh McCarty Jun 8 '12 at 15:13
    
It's the landing page for a site, so I can't calculate the default situation (Today -30). I'll end up with two pages. All fine, but less elegant. –  Marc D Anderson Jun 8 '12 at 15:17
    
This turned out to be the best way to go. I have two pages now: * The landing page shows the default view, which is today and up to 30 days back. * A second page takes a StartDate and EndDate on the Query String and filters for that date range. –  Marc D Anderson Jun 8 '12 at 19:35

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.

share|improve this answer

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: &lt;Geq&gt;&lt;FieldRef Name=&quot;Created&quot;/&gt;&lt;Value Type=&quot;DateTime&quot;&gt;{DateFrom}&lt;/Value&gt;&lt;/Geq&gt;

share|improve this answer
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.

share|improve this answer
    
I'm trying to do this in a DVWP (added into the post above), so this syntax isn't available to me, but nice. –  Marc D Anderson Jun 8 '12 at 14:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.