SharePoint Frustrations #1: The undocumented “IncludeTimeValue” CAML attribute

I’m planning on doing some posts about frustrating things I have encountered (and still do!) during my SharePoint development efforts. Here’s the first one:

Last year while working on a MOSS 2007 project for one of our customers I stumbled on what I thought was a bug in SharePoint 2007. I had created a custom list that was filled with Electronic Program Guide (EPG) information for the streaming video media that that site contained. I then created a Webpart that used ASP.Net AJAX to continually show the actual EPG information below the video stream.

In order to obtain the EPG items I used a CAML query to query the EPG list. The list was simply a custom list containing amongst others a column of type DateTime that was called "ProgramEnd". As the name suggests it contained the time the program ended. I then used the following code to create a query that was supposed to obtain the currently broadcasted item and all future items.

   1: const string EPG_QUERY_TEMPLATE = @"
   2: <Where>
   3:  <Geq>
   4:   <FieldRef Name='ProgramEnd' />
   5:   <Value Type='DateTime'>{0}</Value>
   6:  </Geq>
   7: </Where>"; 
   8:  
   9: SPQuery query = new SPQuery();
  10: query.Query = String.Format(EPG_QUERY_TEMPLATE,
  11:     SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now)); 
  12: // ...

To my surprise this query always returned too many items. Further investigation showed that time part of the query seemed to be ignored completely, so it returned the items as if no time was specified! So instead of getting the current item and all future items it returned all items broadcasted for that day.

After wasting a lot of time debugging, using the U2U Caml Query Builder and looking for an answer / solution on the internet I gave up and wrote a quick hack around it, which fortunately wasn’t very difficult. It would just run the query and run the results through some additional code that checked the ProgramEnd DateTime field and filter out the wrong items, like this:

   1: SPListItemCollection results = ...; // The results from the query mentioned above
   2: // Trim the results to only include the current and future items
   3: List<SPListItem> trimmedItems = new List<SPListItem>();
   4: foreach (SPListItem result in results)
   5: {
   6:     DateTime programEnd = (DateTime)result["ProgramEnd"];
   7:     if (programEnd >= DateTime.Now)
   8:         trimmedItems.Add(result);
   9: } 

Fortunately this worked just fine and the customer was happy. I was not… 😦

Today while surfing the Net I stumbled on this entry in the MSDN forums, from which I learned it wasn’t a bug, but that you need to included the "IncludeTimeValue" attribute to the CAML query, like this:

   1: <Where>
   2:  <Eq>
   3:   <FieldRef Name='programEnd' />
   4:   <Value Type='DateTime' IncludeTimeValue='TRUE'>
   5:    2008-07-30T12:00:00Z
   6:   </Value>
   7:  </Eq>
   8: </Where> 

If only I had known it was this simple… What bothers me is that this little, but very important attribute seems to be totally undocumented. I couldn’t find any information about it in the WSS / SharePoint SDKs.

Ofcourse 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 about this way back in October 2007, only a few months after I searched for it. Even Karine Bosch, U2U’s "CAML Girl" and author of the famous U2U CAML Query Builder, says she only recently found out about this. Fortunately she has included support for the "IncludeTimeValue" attribute in her latest version of the U2U Caml Query Builder, which I know a lot of SharePoint developers use to construct and test their CAML queries.

I also noticed that someone called puneetspeed has added some Community Content to the online SharePoint SDK’s SPQuery docs explaining this issue. So hopefully Microsoft will add information about the "IncludeTimeValue" attribute to the official SDK text in the near future.

Posted on July 30, 2008, in developing, microsoft, sharepoint, Uncategorized and tagged , , , . Bookmark the permalink. 9 Comments.

  1. Wow, this article could have been written by me! I also feel frustrated quite often when working on Sharepoint. I didn’t know about this, but I also haven’t yet encountered a situation where I needed it.

  2. Hi Leon,
    I alreay read your post before you left the comment on my blog 🙂
    Thanks for mentioning the U2U CAML Query Builder!
    Kind regards,
    Karine

  3. Hi,
    I have spent a whole day on this, but not getting the results I want. I appreciate it if you can help. It returns all the files in the folder. Here’s my code,

    string sourceUrlProcessed = StringHelper.ProcessStringField(executionContext, SourceUrl);
    using (SPSite sourceSite = new SPSite(sourceUrlProcessed))
    {
    using (SPWeb sourceWeb = sourceSite.OpenWeb())
    {

    SPFolder sourceFolder = sourceWeb.GetFolder(sourceUrlProcessed);
    SPList sourceList = sourceWeb.Lists[sourceFolder.ParentListId];

    SPFolderCollection subFolders = sourceFolder.SubFolders;
    string stDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(this.ToMoveStartDate);
    string endDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(this.ToMoveEndDate);

    foreach (SPFolder subFolder in subFolders)
    {
    SPQuery query = new SPQuery();
    query.Query = “” + stDate + “” + endDate + “”;

    query.Folder = subFolder;
    SPListItemCollection files = sourceList.GetItems(query);
    DebugHelper.Log(“Sourcefolder= ” + query.Folder.Name);
    DebugHelper.Log(“Count= ” + files.Count);

    foreach (SPListItem file in files)
    {
    DebugHelper.Log(“Filenme= ” + file.Name);
    }
    }
    }
    }

  4. Sorry, I posted wrong code earlieir.Anyways, I finally got it to work. I did not know that the xml would be case sensitive. I added scope=Recursive and changed case on the query xml, and boom! It returned correct results. I did use the U2U CAML query builder to get the correct xml.
    Thanks,

    using (SPSite sourceSite = new SPSite(sourceUrlProcessed))
    {
    using (SPWeb sourceWeb = sourceSite.OpenWeb())
    {
    SPFolder sourceFolder = sourceWeb.GetFolder(sourceUrlProcessed);
    SPList sourceList = sourceWeb.Lists[sourceFolder.ParentListId];
    SPFolderCollection subFolders = sourceFolder.SubFolders;
    string stDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(this.ToMoveStartDate);
    string endDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(this.ToMoveEndDate);
    SPQuery query = new SPQuery();
    query.Query = “” + stDate + “” + endDate + “”;
    query.ViewAttributes = “Scope=’Recursive'”;
    query.ViewFields = BuildViewFieldsXml(“Title”, “Created”, “ID”, “Name”, “State”, “FVDate”);
    SPListItemCollection sourceItems = sourceList.GetItems(query);
    }
    }

  5. Hi All,
    Does this work with the tag too? I need to isolate conflicting appointments being posted in a calendar list (and of course I need to check for fRecurrence) and I’m using the tag and setting the SPQuery’s CalendarDate property to a date and time, but it still returns some rubbish results :/. I’m having to do this programmatically currently, but would be nicer in a CAML query. I have tried to use the IncludeTimeValue in my query, but it still get spurious results back.

    Any help would be great!!

    Thanks
    Andy

  6. Dear sir,

    THANK YOU, I’ve been scratching my head for a few hours (thankfully just that few) over this one. Ahh, what a difference a few characters can make – IncludeTimeValue=’TRUE’ – sheesh.

    Thanks again, Ken

  7. Hey colleague :P,
    Nice one

    IncludeTimeValue is thankfully well documented now.
    I just needed the SPUtility.CreateISO8601DateTimeFromSystemDateTime() to make it work.

    Greetings,
    Higo

  1. Pingback: latest android updates

  2. Pingback: Bransons Guide

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

%d bloggers like this: