My Techie Blog

  Home  |   Contact  |   Syndication    |   Login
  110 Posts | 5 Stories | 406 Comments | 0 Trackbacks


Article Categories


Post Categories




ASP.NET Resources

BizTalk Server

Blogs I read

Free Books

How to




JSLink - SharePoint




Mobile BI

MS Business Inteliigence

Reporting Services

Responsive/MVC ASP.NET


Sharepoint Resources/Tools

SharePoint 2010 Branding

SharePoint 2010 Videos

SharePoint 2013

SharePoint Online



SQL Server

Sql Server 2012


Web Optimization Tools

Web Performace Tools

Windows 8

Windows Azure

Windows Azure

Windows Phone




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

<OrderBy><FieldRef Name="Column3" Ascending="TRUE"/>
<FieldRef Name="Modified"/>
<Value Type="Text">

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


# re: Subtracting or adding Dates in your CAML quey filters 12/26/2007 3:26 PM Frank
It's incredible, but it's very useful!
Thanks for sharing!

# re: Subtracting or adding Dates in your CAML quey filters 2/25/2008 10:51 PM Chris
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 2/26/2008 11:56 AM Patrick
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 11/3/2008 8:37 PM srikanth
it's very nice.........(very useful)

# re: Subtracting or adding Dates in your CAML quey filters 2/19/2010 3:25 PM Juan Trujillo

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.

<FieldRef Name="Modified" Type="Date"/>
<FieldRef Name="PermMask"/>
<FieldRef Name='Modified' />
<Value Type="Date">
<Today OffsetDays="-180" />
<FieldRef Name='AssetType' />
<Value Type='Lookup'>In Storage</Value>
<FieldRef Name='Modified' Ascending='False' />

# re: Subtracting or adding Dates in your CAML quey filters 3/14/2010 6:40 PM Patrick
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.

Post A Comment