Subtracting or adding Dates in your CAML quey filters

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;"

Print | posted on Tuesday, November 20, 2007 4:27 PM

Feedback

# re: Subtracting or adding Dates in your CAML quey filters

Left by Frank at 12/26/2007 3:26 PM
Gravatar It's incredible, but it's very useful!
Thanks for sharing!

# re: Subtracting or adding Dates in your CAML quey filters

Left by Chris at 2/25/2008 10:51 PM
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.

# re: Subtracting or adding Dates in your CAML quey filters

Left by Patrick at 2/26/2008 11:56 AM
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.

# re: Subtracting or adding Dates in your CAML quey filters

Left by srikanth at 11/3/2008 8:37 PM
Gravatar it's very nice.........(very useful)

# re: Subtracting or adding Dates in your CAML quey filters

Left by Juan Trujillo at 2/19/2010 3:25 PM
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>

# re: Subtracting or adding Dates in your CAML quey filters

Left by Patrick at 3/14/2010 6:40 PM
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.

Your comment:





 
 

Copyright © Patrick.O. Ige

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski