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

oQuery.Query = "" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today.Subtract(new TimeSpan(1))) + ""

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

This should fix the problem.