All Day Events and GMT

  • [アーティクル]

I am observing a weird quirk with All Day events in a SharePoint calendar. The problem is the “All Day” flag for the “Event” content type. The date values are stored differently depending on whether this flag is set. If you report on the list with a tool like Reporting Services then you get a start date that starts at 8pm the previous day.

I dug around and found an internal article (can’t link, sorry) that explained some of this.

“These date values are offset currently by the GMT offset, but the value are stored in SQL for the all-day events without being offset. Normal events (non-all day events) are stored using the GMT offset.”

My offset is GMT – 4 / UTC –5 since I’m on the East Coast.  The result is that dates get stored with the GMT time portion for all day events. E.g., look at the SSRS report below.

image

The calendar event is listed with a start time of 4/1/2013 in the calendar with the time set to 12am (00:00). The report retrieves the GMT value which is 12am – 4 = 8pm of the previous day. The end date is supposed to be 4/1 with an end time of 11:59pm; subtract 4 you get 7:59pm.

As always, this can be fixed with code but my customer needs no code solutions so they can maintain it themselves.

Some options:

  • Report using views instead of report builder. SharePoint will honor the way it stores the values.
  • Tell users to not use All day events going forward. Instead use a start time of midnight and an end time of 11:59pm.
  • In Report Builder you can still use a parameter but you would need to reference the start date +1 to get an accurate begin date.