How to modify date format in SharePoint

SharePoint out of the box provides multiple locales. You can select the one which would be used to format dates, amounts and other culture specific values in your site. However, you can’t change date format, specifying some format string. That’s why, for example, here in Latvia we are in trouble! :) The default format, what SharePoint provides for Latvia is yyyy.MM.dd. It’s correct from point of view of ISO standard. But customers here prefer using dd.MM.yyyy. That’s why, for example, in Windows this format is set during installation, overriding default ISO one for Latvian locale. There are two ways of solving this problem in SharePoint: to describe the customer why using yyyy.MM.dd. is better in his everyday work, or set German locale as default one in SharePoint. German locale contains required date format (dd.MM.yyyy), but of course it contains German names for weekdays and months as well - you will probably see these in calendar controls and schedules in your site. So the customer could choose less evil from these two. This weekend I was investigating this issue from programmer’s perspective, and had found an interesting workaround. In this post I will describe it in more detail.

The first idea that came to my mind was – ok, SharePoint uses .NET 3.0, ASP.NET controls and so on… so it probably should use CultureInfo for locales as well. So we may use CultureAndRegionInfoBuilder to create our custom locale, inheriting from Latvian. This class is described well here: http://www.codeproject.com/books/CustomCultures.asp. As you can see from this article, you cannot specify LCID for newly created locale, because LCIDs are closed technology now. BUT, SharePoint uses LCID in SPRegionalSettings to reference current locale. That’s why you cannot use custom locales with SharePoint. You can use only ones, which have valid LCID. That’s why we cannot create new locale inheriting from Latvian and use it in SharePoint site, but we should customize existing Latvian locale.

using System.Globalization;
using Microsoft.SharePoint;

static void Main(string[] args)
{
  try
  {
    CultureAndRegionInfoBuilder.Unregister("lv-LV");
  }
  catch{}

  CultureAndRegionInfoBuilder carib = new CultureAndRegionInfoBuilder("lv-LV", CultureAndRegionModifiers.Replacement);
  carib.GregorianDateTimeFormat.ShortDatePattern = "dd.MM.yyyy";
  carib.Register();
}

This code will modify ShortDatePattern parameter of Latvian locale, setting it to dd.MM.yyyy. You should reset IIS after executing the code.
Then you will see an interesting effect – the code had affected date format in date controls of edit forms, but nothing had changed in lists and display forms! Analyzing SharePoint code with Reflector I’ve found out, that SharePoint is not using .NET CultureInfo in that forms. It calls SPUtility.FormatDate method, providing current SPWeb with other parameters. This method grabs LCID from SPWeb, and goes to SharePoint COM library, asking to format specified date in specified format. This COM library of course is not using .NET CultureInfo, and that’s why it knows nothing about our changes in date format – it uses default date and time format strings for the locale having provided LCID.
I’ve tried to find out where this default short date format string is stored. As I understand, Windows has multiple possible short date format strings specified for each locale. You can see these in “Short date format” drop-down, going to Settings -> Control Panel -> Regional and Language Options -> Customize -> Date on your Windows Server 2003. If you specify some new format, it will be shown as the last one in the list of available for current culture. But it looks like SharePoint COM library is always using the first short date format available for the locale. For Latvian it will always use yyyy.MM.dd. I’ve spent lot’s of time Googling, investigating Windows registry and file system, trying to find out where these default date formats for each locale are stored. Of course I’ve found nothing – this part of Windows is not documented :)
So, the conclusion is – changes in CultureInfo on the server do affect all .NET side of SharePoint but have no effect on functionality, implemented by using COM interoperability. As I understand, COM is used while formatting dates and numbers in lists and display forms. May be it is implemented this way to improve performance. Obviously, this is a well known SharePoint problem, which you may encounter, for example, while developing custom field types. Your custom field type class will not be even instantiated, while rendering list forms. Only value stored in database and XML schema for the field will be analyzed. I’m planning to describe this in more detail in some future post. But now let’s return to modifying date display format in our SharePoint site.

If we want to see dates in the same format in all SharePoint site, we could take some existing culture as a base, and modify it, to match specific settings of our locale. SharePoint COM side will use default parameters of this culture, but .NET – customized ones. As I’ve described earlier, looks like COM side is used only for formatting date and time in short format, and numbers. You should take it in mind, while selecting a culture to use as a base one. I’ve done it using the following code:

CultureInfo lvci =  new CultureInfo("lv-LV", false);
DateTimeFormatInfo lvdtf = lvci.DateTimeFormat;
NumberFormatInfo lvnf = lvci.NumberFormat;
foreach(CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures))
{
  if(!ci.IsNeutralCulture)
  {
    DateTimeFormatInfo dtf = ci.DateTimeFormat;
    NumberFormatInfo nf = ci.NumberFormat;
    if(ci.LCID == lvci.LCID  (dtf.ShortDatePattern == "dd.MM.yyyy" && nf.NumberDecimalSeparator == lvnf.NumberDecimalSeparator
      && nf.NumberGroupSeparator == lvnf.NumberGroupSeparator))
      Console.WriteLine("{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}",
        ci.LCID,
        ci.Name,
        ci.EnglishName,
        ci.DateTimeFormat.ShortDatePattern,
        dtf.TimeSeparator,
        ci.NumberFormat.NumberDecimalSeparator,
        ci.NumberFormat.NumberGroupSeparator,
        nf.CurrencyPositivePattern,
        nf.CurrencyNegativePattern,
        nf.NumberDecimalDigits,
        nf.NumberDecimalSeparator,
        nf.NumberGroupSeparator,
        nf.PercentDecimalSeparator);
  }
}

As a result you’ll get a list of cultures, which have dd.MM.yyyy as a short date pattern, and number separators equal to Latvian locale. Some additional number formatting parameters will be displayed in the output, just to help you to choose more appropriate culture from the list. I’ve chosen Norwegian “nb-NO” as a base. Now executing the following code will setup this culture to be used in our site replacing Latvian locale:

try
{
  CultureAndRegionInfoBuilder.Unregister("nb-NO");
}
catch{}

CultureAndRegionInfoBuilder carib = new CultureAndRegionInfoBuilder("nb-NO", CultureAndRegionModifiers.Replacement);
carib.LoadDataFromCultureInfo(new CultureInfo("lv-LV"));
carib.LoadDataFromRegionInfo(new RegionInfo("lv"));
carib.GregorianDateTimeFormat.ShortDatePattern = "dd.MM.yyyy";
carib.Register();

CultureInfo ci = new CultureInfo("nb-NO");
using(SPSite site = new SPSite("http://localhost"))
{
  using(SPWeb web = site.RootWeb)
  {
    web.Locale = ci;
    web.Update();
  }
}

Finally, you should restart IIS to see the difference.

As you can see, all dates now are display as dd.MM.yyyy. But this is obvious, because now we are using Norwegian locale. The most interesting part is that calendar controls and views for lists have Latvian names for months and weekdays displayed. So now we are using Norwegian locale on our site, but all user interfaces are in Latvian!

And finally, don’t forget to setup correct sorting order and time zone in regional settings. Luckily, these are not dependant from regional settings used on the site. :)

Hope this helps!