Saturday, November 17, 2012

Locale bug in ddwrt:FormatDate not resolved for external data

XsltListViewWebPart is a powerful out-of-the-box SharePoint 2010 capability to render data, using Xslt to format the data display as you like. One of the standard Xslt functions, ddwrt:FormatDate, exhibits an annoying bug when used within XsltListViewWebPart. In case the locale of SharePoint server is other than US, then day and month are switched if day is < 13. The rootcause is – according to Microsoft - that ddwrt:FormatDate expects the datetime value to be in UTC format, but in reality XsltListViewWebPart preformats datetime values to the SharePoint server Locale before the Xslt-transformation takes place.
The SharePoint 2010 February CU 2012 contains an hotfix for this bug, and introduces the ListViewWebPart property ‘EnableOrginalValue’.
<property name="EnableOriginalValue" type="bool">True</property>
The effect of this property setting is that XsltListViewWebPart will include multiple formats per datetime value in the preformatted data. By using the ISO8601 datetime variant, the display bug can be resolved:
<xsl:value-of select="ddwrt:FormatDate(@Name,'.ISO8601')]),1033, 3)" />
However, this does not hold in case of external data. XsltListViewWebPart ignores the ‘EnableOriginalValue’ flag when rendering external data; and does not preformat the 'ISO8601' variant. Inspecting the sourcecode of super class DataFormWebPart proofs this to be true:
Conclusion is that XsltListViewWebPart by design does not support the hotfix for external data. Reasoning is that SharePoint BCS expects datetime values received from external systems to be in UTC format. And if not in UTC (DateTime.Kind unequal ‘UTC’), SharePoint BCS converts the received DateTime value into UTC:
"External systems commonly standardize on Coordinated Universal Time (UTC) as the time zone in which to store data. While this works well for data storage, end users prefer to work with times shown in their local time zone. External lists and the External Data Web Parts tackle this problem by converting time zones before presenting DateTime fields to end users, as well as before submitting DateTime values to the external system.
After retrieving a DateTime field from an external system, the BDC runtime examines the DateTime.Kind property to determine its time zone. If it is UTC or Unspecified, no action is taken. If it is marked as Local, BDC converts it to UTC by applying an offset based on the SharePoint front-end web server’s time zone, as specified by its Windows settings.
Now that the time zone has been standardized to UTC, the external list (aka XsltListViewWebPart) examines the user profile settings for the current user; if a time zone has been specified, the time is converted from UTC to the user's time zone. If a time zone has not been specified (which is the default state), the time zone of the site on which the external list is hosted is used to convert the external data. The converted times are then presented to the user in the UI in a format (for example dd/mm/yyyy hh:mm:ss) based on the site locale."
Source: Professional Business Connectivity Services – Scot Hillier
Thus external data received within XsltListViewWebPart will only contain UTC datetime values; therefore the issue should not occur; and the hotfix is on purpose disabled for external datasource.
But this is a design fault. We have a situation in which we retrieve SAP data [using Duet Enterprise + SharePoint BCS] in UTC format; and then still encounter the issue in case day < 13. Not surpisingly, UCT merely applies an offset in hours to the locale datetime; day and month remain in the same range; with at maximum 1 day earlier or later. Therefore the ddwrt:FormatDate also manifest itself for UTC datetime in case day < 13. As the XsltListViewWebPart hotfix on purpose does not fix that, we need an own Xslt FormatDate function to properly render the UTC datetime received from external system.

No comments:

Post a Comment