SharePoint - all daytime events behave differently in a CAML request

Question

In the list of SharePoint calendars, I create two events with today's date. I do one of them throughout the day, and the other set a start time of 12:00, and an end time of 11:55 pm.

When I create a CAML query (in this case w / "U2U CAML Query Builder"), I see some strange behavior. When my request 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 NOT event is returned, marked as “All Day Event”:

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

Studying the results from the query building 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 handle these two events the same way? Could this be a time zone?

EDIT: More info, I think it could be a timezone problem. I found 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 that the date is now fourth, not fifth)

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

Then both events return, but if I get to 20:01, I will lose the event all day long. When I go at 20:01, I lose all day. Does anyone know where I can find a detailed description of this behavior?

EDIT2: I embarrassed myself; fixed first edit.

Answer 1

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

However, for all daytime events, it saves time (00:00 - 23:59:00) on local local sites.

As you already understood - I believe that you found an error in the way SharePoint interprets the request and forgets that the events throughout the day are local time.

I think you could make an unpleasant workaround by running a query for

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

This poster has similar SO problems - a SharePoint event all day gives an implicit result

And this will give you a deeper understanding of how borked time zones are in SharePoint. SharePoint Web Services and time and time for UTC and time for games

And if this is not enough for ya, then look at the created / modified dates using the object model and marvel at how they are reported as Local time for ordinary events and UTC for all daytime events!

Answer 2

 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, for example:

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

This should fix the problem.

Answer 3

When processing the results of a CAML request, this will solve the problem, but is not 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 that you are not restricting your search by day. obviously, in this case, it would not help if you did not expand the search options to include a wider range than you really need.

I know that this is not exactly what the poster is looking for, but it can help others who find this page ... there is not much documented about this issue.

In particular, this fix will work if you request by month, and only display calendar items that exist this month. In this case, CAML will return the last few days of the previous month and the first few of the next month in any case, so you will not lose data that is compensated for one day. (using <Month /> )

Answer 4

It worked for me.

 <Where> <Or> <Or> <And> <Eq> <FieldRef Name='fAllDayEvent' /> <Value Type='AllDayEvent'>1</Value> </Eq> <Geq> <FieldRef Name='EndDate' /> <Value Type='DateTime'> <Today /> </Value> </Geq> </And> <DateRangesOverlap> <FieldRef Name='EventDate' /> <FieldRef Name='EndDate' /> <FieldRef Name='RecurrenceID' /> <Value Type='DateTime' IncludeTimeValue='TRUE'> <Today /> </Value> </DateRangesOverlap> </Or> <Geq> <FieldRef Name='EventDate' /> <Value Type='DateTime' IncludeTimeValue='TRUE'> <Today /> </Value> </Geq> </Or> </Where>