SharePoint - all day events behave differently in CAML query

Question

In a SharePoint calendar list I create two events w/ today's date. One I make an all-day event, the other I set the start time to be 12 AM and the end time to be 11:55 PM.

When I create a CAML query (in this case w/ "U2U CAML Query Builder") I'm seeing some weird behavior. When my query is a simple "OrderBy" both events are returned.

When I execute the following query that searches for events that are greater than or equal to today, only the event that is NOT marked as an "All Day Event" is returned:

<Where>
    <Geq>
        <FieldRef Name='EventDate' />
        <Value Type='DateTime'>2009-10-05T00:00:00Z</Value>
    </Geq>
</Where>

Examining the results from the query builder tool I see that the values for EventDate (the internal name of the Start Time column) are identical (2009-10-05 00:00:00).

Why does SharePoint treat these two events identically? Could it be a time zone issue?

EDIT: More info, I think this might be a time zone issue. I discovered the "IncludeTimeValue" attribute of the Value element - described here: MSDN. I am on the East Coast (currently GMT - 4 hours). If I edit the Value element as follows: (note the date is now the 4th, not 5th)

<Value Type='DateTime' IncludeTimeValue='True'>2009-10-04T20:00:00Z</Value>

Then both events are returned, but if I go up to 20:01 then I lose the all-day event. When I go to 20:01 I lose the all-day event as well. Anyone know where I can find a thorough description of this behavior?

EDIT2: I confused myself; corrected the first edit.

Answer 1

SharePoint stores the date/time in UTC (aka GMT or Zulu) and when displaying first converts it to the sites local time zone.

However for All day events it stores the times (00:00 to 23:59:00) in the sites LOCAL time zone instead.

As you've already figured out yourself - I believe you've found a bug in the way the SharePoint interprets the query and its forgetting that all day events are local time.

I guess you could do a nasty workaround this by doing a query for

EventDate >= SomeDate OR AllDayEvent = True AND EventDate >= SomeDate - 4hrs

This poster has similar problems SO - SharePoint all day event gives obscure result

And this will give you some more insight into how borked time zones are in SharePoint SharePoint Web Services and UTC time fun and games

And if that's not enough frustration for ya then look at Created/Modified dates via the object model and marvel at how they are reported as Local time for normal events and UTC for all day events!

Answer 2

When processing the results of a CAML query, this will solve the issue, but isn't ideal:

foreach (SPListItem item in _items){

     //   ... loop for processing items returned from CAML query, 
     //       code unrelated to UTC conversion excluded

      var localSDate = Convert.ToDateTime(item["StartDate"].ToString());

      if (Convert.ToBoolean(item["fAllDayEvent"])){
            localSDate = localSDate.ToUniversalTime();
      }
}

NOTE: this fix assumes you aren't limiting the search by day. obviously in that case it wouldn't help, unless you expanded the search parameters to include a wider range than you actually need returned.

I know it's not exactly what the poster is looking for, but this could help others that find this page...there isn't a lot documented on this issue.

Specifically this fix will work if you are querying by month, and only displaying calendar items that exist in that month. In this case, CAML will return the last few days of a previous month and the first few of the next month anyways, so you won't lose the data that is offset by a day. (using <Month />)

Answer 3

oQuery.Query = "<Where><Geq><FieldRef Name='EventDate' /><Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today.Subtract(new TimeSpan(1))) + "</Value></Geq></Where>"

Adding a Timespan value like:

SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today.Subtract(new TimeSpan(1)))
"2010-08-23T23:59:59Z"

This should fix the problem.