SharePoint XSLT FormatDate Issue With Different DateTime Formats

Recently, I came across a weird issue. Our SharePoint site was running using the Locale, US [English]. We’re supposed to change it to UK i.e., to change the site’s DateTime format from mm/dd/yy to dd/mm/yy. One can easily do that in the Regional Settings option of their site. However, after the change, we noticed a discrepancy in the DateTime displayed in all our custom XSLTs.Say, for a Date, Friday, January 02, 2015, the value displayed after the change was Sunday, February 01, 2015! Clearly, it was the issue with the date format. Once the value 01/02/2015 [mm/dd/yyyy] got changed to 02/01/2015 [dd/mm/yyyy], something went wrong in our custom view WebPart. However, a Date value, where the date was more than 12, say, 20/12/2014 [dd/mm/yyyy] was being correctly displayed as, Friday, December 19, 2014 which, again, was very surprising. The current settings of the custom WebPart was:

<span class="submittedby">
    <xsl:text>Author:</xsl:text>
    <xsl:value-of select="@AuthorBy" disable-output-escaping="yes"/>  
    <xsl:text> on </xsl:text>
     <xsl:value-of select="ddwrt:FormatDate(string(@SomeDateField), 2057, 3 )"/>
</span>

where, &#xA0 (with semi-colon), denotes a single space character.

I was surprised. The LCID 2057[UK] was already being passed to the function, FormatDate, even when the site’s locale was set to US. Then why the webPart would behave correctly for the locale US and not for UK? It took me a while to figure out that the LCID [2057] that was being used. only affects the output from the method, FormatDate. It however, expects the DateTime in a given format only as input, which is, mm/dd/yyyy. So when the site locale was US, everything was working fine as the date returned by SharePoint was already in this format but, when I changed it to UK things got different.

One important thing to not here is, that the FormatDate function tolerates the obvious error like 20/12/2014 [dd/mm/yyyy] which automatically became, Friday, December 19, 2014, which indeed, is quite frustrating. Anyway, since we knew that in our case, the site locale would always remain UK, we applied a hard-code fix to this problem. Since, the method expects DateTime of type string, so instead of directly passing the Date field to it, we decided to construct the DateTime from dd/mm/yy[UK returned by SharePoint] to mm/dd/yyyy[US which the method accepts as the only input format] using the string-split approach. Following is the modified code snippet.

<span class="submittedby">
    <xsl:text>Author:</xsl:text><xsl:value-of select="@AuthorBy" disable-output-escaping="yes"/> <xsl:text> on </xsl:text>

    <!--original date in dd/mm/yyyy without time-->
    <xsl:variable name="dateFirst" select="substring-before(@SomeDateField,' ')"></xsl:variable>

    <!--date-->
    <xsl:variable name="date" select="substring-before($dateFirst,'/')"></xsl:variable>

    <!--month & Year part-->
    <xsl:variable name="dateRemaining" select="substring-after($dateFirst,'/')"></xsl:variable>

    <!--month-->
    <xsl:variable name="month" select="substring-before($dateRemaining,'/')"></xsl:variable>

    <!--year-->
    <xsl:variable name="year" select="substring-after($dateRemaining,'/')"></xsl:variable>


    <!--construct date as mm/dd/yyyy-->
    <xsl:variable name="pubDate" select="concat($month, '/', $date, '/', $year)"></xsl:variable>

    <!--use the date from the variable @pubDate-->
     <xsl:value-of select="ddwrt:FormatDate($pubDate, 2057, 3 )"/>

</span>

After this fix, everything apparently, returned back to normal, however we know that if tomorrow we try to use the same XSLT to a different site whose locale is not set to UK then, the same issue is bound to re-generate and we have to rework on our split logic.

Get the CultureInfo of a SharePoint Online site using Client Object Model C#

CSOM code gets executed from client machine and there’s a fair amount of chance that the current machine culture will not match with that of SharePoint Online site. Now, the problem is that for a ListItem of any List, SharePoint returns DateTime value in a string format. So, if you try to convert the string to a DateTime, you’ll get a FormatException that, the String was not recognized as a valid DateTime.

So, the solution is to get the current culture of the SharePoint Online site, and then apply the same to the current thread. To do that you need to query the LocaleId of the current site which, you can then use to get the CultureInfo.

//Getting and applying the SharePoint site culture to the working thread 
Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo(Convert.ToInt32(ctx.Web.RegionalSettings.LocaleId));

I have successfully tested this code for the following two conditions. My machine’s culture was en-US [MM/DD/YYYY] :-

  • en-CA [DD/MM/YYYY]
  • en-ZA [YYYY/MM/DD]

You can also see the List of CultureInfo.

Get the UTC DateTime of a SharePoint Field using Client Object Model C#

Following is just a sample to get the current site’s last modified date in UTC. You can apply the same to any DateTime fields in SharePoint. Here, SharePoint itself returns the DateTime in UTC as per its regional setting.
ClientContext ctx = new ClientContext(weburl);
ctx.Credentials = new SharePointOnlineCredentials(userName, passWord); 
RegionalSettings regionalSettings = ctx.Web.RegionalSettings;

ClientResult<DateTime> utcTime = regionalSettings.TimeZone.LocalTimeToUTC(ctx.Web.LastItemModifiedDate);

ctx.ExecuteQuery();

utcTime.Value is your DateTime in UTC.