Geeks With Blogs
My Techie Blog Knowledge Creation is Essential to Business, and Communication is Essential to Knowledge Creation

I had a link list on a WSS v3 site and i wanted to  show this list on other sites and subsites.
So i decided to plug to the datasource and extract the links using a DataView Webpart.
It all went well and good but i wanted another view that will show  users latest links added a week ago for example .

So of course decided to use a filter using the
Field Name - Created 
Comparison - Greater that or Equal to
Value - [CurrentDate] 

But there was no way to change the CurrentDate from the Filter Criteria Menu because i need to do
CurrentDate - 5 (which means subract 5 days  from the the CurrentDate.
Well i said to myself what left THE CODE

My first approach was use this caml query like so below but didn't work

<View><Query>
<OrderBy><FieldRef Name="Column3" Ascending="TRUE"/>
</OrderBy>
<Where>
<Geq>
<FieldRef Name="Modified"/>
<Value Type="Text">
<[Today]-4]/>
</Value>
</Geq>
</Where>
</Query>
</View>
 

Until i found out i could use an OffsetDays attribute like below :

<Value Type="DateTime"><Today OffsetDays="-8"/></Value>


Which means that 8 is subtracted from the date that the query filters on.

And that did the trick


<SharePoint:SPDataSource runat="server" SelectCommand="&lt;View&gt;&lt;Query&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;Column3&quot; Ascending=&quot;TRUE&quot;/&gt;&lt;/OrderBy&gt;&lt;Where&gt;&lt;Geq&gt;&lt;FieldRef Name=&quot;Created&quot;/&gt;&lt;Value Type=&quot;Text&quot;&gt;&lt;Today OffsetDays=&quot;-8&quot;/&gt;&lt;/Value&gt;&lt;/Geq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" Posted on Tuesday, November 20, 2007 4:27 PM | Back to top


Comments on this post: Subtracting or adding Dates in your CAML quey filters

# re: Subtracting or adding Dates in your CAML quey filters
Requesting Gravatar...
It's incredible, but it's very useful!
Thanks for sharing!
Left by Frank on Dec 26, 2007 3:26 PM

# re: Subtracting or adding Dates in your CAML quey filters
Requesting Gravatar...
How do you add the queries into the SharePoint site?

I do not really want to type the code view one i.e. &quot;/&gt.... etc.

Left by Chris on Feb 25, 2008 10:51 PM

# re: Subtracting or adding Dates in your CAML quey filters
Requesting Gravatar...
Chris you can use sharepoint designer and plug into the code view to do this.Just follow the instructions above and you should be fine.
Left by Patrick on Feb 26, 2008 11:56 AM

# re: Subtracting or adding Dates in your CAML quey filters
Requesting Gravatar...
it's very nice.........(very useful)
Left by srikanth on Nov 03, 2008 8:37 PM

# re: Subtracting or adding Dates in your CAML quey filters
Requesting Gravatar...
Hi,

Any ideas on how can I format dates in CAML ? I have this CAML but I need to format the dates to MM/yyyy format.

<View>
<ViewFields>
<FieldRef Name="Modified" Type="Date"/>
<FieldRef Name="PermMask"/>
</ViewFields>
<Query>
<Where>
<And>
<Geq>
<FieldRef Name='Modified' />
<Value Type="Date">
<Today OffsetDays="-180" />
</Value>
</Geq>
<Eq>
<FieldRef Name='AssetType' />
<Value Type='Lookup'>In Storage</Value>
</Eq>
</And>
</Where>
<OrderBy>
<FieldRef Name='Modified' Ascending='False' />
</OrderBy>
</Query>
</View>
Left by Juan Trujillo on Feb 19, 2010 3:25 PM

# re: Subtracting or adding Dates in your CAML quey filters
Requesting Gravatar...
See this
To ensure that dates are formatted correctly across your WSS or MOSS site there are a couple of settings you may need to change.

First of all the regional settings should reflect the current locale. This can be changed from Site Actions > Site Settings > Regional Settings and ensuring the locale is correct. This should ensure all controls that display dates are formatted correctly.

If you are using XSLT to display dates (such as in a data view web part) you may need to update these also. One way of doing this is to take advantage of the format-date extension function provided by MSXML. This is included in the XSL for the dataview by default, but you can use this and other MSXML extension functions by including a reference to the Microsoft XPath extension functions (urn:schemas-microsoft-com:xslt) at the top of your XSL stylesheet i.e.

<xsl:stylesheet ... xmlns:msxsl="urn:schemas-microsoft-com:xslt">

You can then use the format-date (and format-time) functions like so:

<xsl:value-of select="msxsl:format-date(@_DCDateCreated, 'dd/MM/yyy')"/>

This will format the date in the form 30/03/2007. The Microsoft XPath extension functions page contains a full list of formatting characters.
Left by Patrick on Mar 14, 2010 6:40 PM

Your comment:
 (will show your gravatar)


Copyright © Patrick.O. Ige | Powered by: GeeksWithBlogs.net | Join free