Friday, August 31, 2012

How to: Set a date with correct time in a SharePoint list item

When adding a SharePoint list item the one field type that has given me most problems is the SPDateTimeField:

First there is the question of entering the date in a format that SharePoint accepts. Then there is the problem of getting the time zone right.

Fortunately, neither of the two problems are actually hard to solve – Microsoft has tried to make life easy for us by supplying the tools we need to solve them quietly and efficiently.

Setting a valid date

SharePoint accepts date entered in ISO8601 format. So all we need to do is to enter the date in the following format:

yyyy-MM-ddThh:mm:ssZ
2012-08-22T07:40:31Z (example)

You could of course do a

string.Format(
  "{0:0000}-{1:00}-{2:00}T{3:00}:{4:00}:{5:00}Z",
  date.Year,
  date.Month,
  date.Day,
  date.Hour,
  date.Minute,
  date.Second);

but that is a bit cumbersome, and you are bound to sometimes get the formatting wrong. Luckily, Microsoft has provided us with the following method, which is found in the Microsoft.SharePoint.Utilities namespace of the Microsoft.SharePoint assembly:

SPUtility.CreateISO8601DateTimeFromSystemDateTime(date);

This creates a string with the correct format. There is also the opposite method that creates a DateTime object:

SPUtility.CreateDateTimeFromISO8601DateTimeString(dateString);

Unfortunately, if you use the above on a random DateTime object to set the date and time of a list item, you will probably see that the time part is offset by a number of hours. This can happen even if you get the DateTime value directly from the same list item that you are inserting it into.

Getting the time part correct

The problem with the time part offset is of course a matter of time zones. There are several places we can fiddle with the time zone, including on the web application and the individual site. But there is a (again Microsoft-provided) simpler way:

web.RegionalSettings.TimeZone.LocalTimeToUTC(date);

This creates a DateTime object with the correct time zone. If you insert this object in the CreateISO… method above, you will get a string that SharePoint interpretes as having the correct time zone. As before, the opposite method also exists:

web.RegionalSettings.TimeZone.UTCToLocalTime(date);

Summary

Setting date and time is always tricky, due to the many different formats and time zones. In the SharePoint object model we have methods that lets us handle these issues in a standardized manner. These methods are available both in the full object model, and in the limited sandboxed object model.

4 comments:

  1. In the definition of Date Submitted, is the Date and Time Format currently Date Only? If so, try changing it to Date & Time. If you have been interested just see here.

    ReplyDelete
  2. Thanks for useful tips, see here to find out more.

    ReplyDelete
  3. Nice concise article, thanks. Actually the format in your first example should use 24-hour time (capital HH): yyyy-MM-ddTHH:mm:ssZ

    ReplyDelete