Nick Grattan's Blog

About Microsoft SharePoint, .NET, Natural Language Processing and Machine Learning

Filtering views by Time and Date

with 11 comments

An earlier blog entry describes how to filter List and Document views by date. However, the [Today] function, by default, returns only the date and not the time. Therefore, this solution does not solve mad-tie’s problem of wanting to filter on items created in the last hour.

CAML queries can include time as well as date when using the [Today] function. This is described by this blog by ucsharp. In CAML when using the [Today] function you can use the “IncludeTimeValue” attribute to specify that time as well as date should be used:

<Value Type=”DateTime” IncludeTimeValue=”TRUE”><Today /></Value>

Now, the problem is that you cannot include this attribute through the SharePoint user interface, but it can be done with SharePoint Designer. So, to create a view that displays items created in the last hour:

  • Create a calculated column called “DatePlusHour” of “Date and Time” type using the “Date & Time” format in the list or document library (as done by mad-tie):

=Created+0.0416666666666667

This takes the date/time of when the item was created and adds one hour (1/24 = 0.4166…).

  • Modify the view (e.g. “All Items”) to filter based on [Today]:

Dtfilter01

Now the CAML query so created needs to be manually edited to include the IncludeTimeValue attribute:

  • Run Microsoft Designer and open the site in which the list or document library resides.
  • Locate the list or document library and open the .aspx file associated with the view (e.g. “AllItems.aspx”.
  • Click the Code tab and locate the CAML query associated with the filter (search on “DatePlusHour”).
  • Edit the CAML query to include the IncludeTimeValue attribute. NOTE: Include spaces exactly as show!

   &lt;Gt&gt;
    &lt;FieldRef Name=&quot;DatePlusHour&quot;/&gt;
    &lt;Value Type=&quot;DateTime&quot; IncludeTimeValue=&quot;True&quot;&gt;
     &lt;Today/&gt;
    &lt;/Value&gt;

It should look like the following:

Dtfilter02

WARNING: If the SharePoint user interface is used to modify the view the IncludeTimeValue attribute will need to be readded using the instructions above.

 

About these ads

Written by Nick Grattan

April 24, 2008 at 1:59 pm

11 Responses

Subscribe to comments with RSS.

  1. Nick,
    I’m having problems with getting this to work. This my first time dealing with CAML. I didn’t add the additional hour to my column but I don’t see how that should affect it. I actually have two fields to filter on; a Begins fiels and Expires field.

    Here is my code. Please let me know what I’m doing wrong. Thanks.

    Where><And><Leq><FieldRef Name=”Begins”/><Value Type=”DateTime”>IncludeTimeValue="True"><Today/></Value></Leq><Geq><FieldRef Name=”Expires”/><Value Type=”DateTime”>IncludeTimeValue="True"><Today/></Value></Geq></And><

    Stacie

    June 26, 2008 at 9:45 pm

  2. Nevermind Nick, I figured it out! Thanks for posting this; it was a huge help!

    Stacie

    June 26, 2008 at 10:03 pm

  3. [...] once I knew what to look for I found some other blogs and forums mentioning this issue. It turns out the UCSharp blog had already blogged [...]

  4. Good idea, but when you reach the element limit and use paging, it doesn’t work anymore.

    Paal

    August 21, 2008 at 10:27 am

  5. i have added IncludeTimeValue as advised and THIS DOES NOT WORK FOR ME, i am traying to filter by date time and only the date is used. Is there any explanation for that.Help Urgently needed
    >
    <FieldRef Name="Escalate_x0020_Time"/>
    <Value Type="DateTime" IncludeTimeValue="True">
    <Today/>
    </Value>

    Tebogo

    March 23, 2009 at 6:46 am

  6. it is now working the problem was the space between

    Type=”DateTime” and IncludeTimeValue=”True

    Tebogo

    March 23, 2009 at 7:55 am

  7. Hello,
    What do you mean by ” the problem was the space between”? Today function seems not working for me :(

    ynuska

    August 31, 2009 at 2:00 pm

  8. </OrderBy

    after doing this also Im getting error while loading web part
    error:

    guru

    May 18, 2010 at 12:57 pm

  9. Excellent tip, thank you!

    Jussi Palo

    January 16, 2012 at 9:09 am

  10. The problem with all of these blogs is that every solution starts with “Use SharePoint Designer to…” Many of us do not have access to SharePoint Designer and are limited to the user interface, so I find that 99% of the answers on these forums are useless to me. Just an observatoin – goes for all SharePoint forums, not just this one.

    John Blaser

    April 19, 2013 at 5:12 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: