Monday, September 17, 2007

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!

54 comments:

Unknown said...

Thanks for the great tip and the investigation work!
As far as I understand, the first part of the workaround should be executed on all servers in the SP farm, since it is actually modifying the system regional settings of the computer.
Will try this out soon and see the results (and, of course, let's hope that quality assurance people will allow use of this hack).

Anton said...

Yes, you are completely right. This code modifies settings for Norwegian locale for all .NET applications on a single server. So if you have a server farm, you should execute this code on each server in the farm.
I don’t think that quality assurance team could have some objections on using this method. Obviously, there is no hack at all, because we are not modifying the registry of the server, or writing some information directly to its memory. We are just setting the other locale on SharePoint and modifying it with the CultureAndRegionInfoBuilder class. And this is the way, how Microsoft recommends creating custom locales or modifying default ones. Of course you should keep in mind that changes applied to Norwegian locale in this sample, will affect all .NET applications running on the server.
Additionally I have no time to test this setup in all possible scenarios. I have just checked some WSS web forms, and it looks like everything works fine there. If you will check it in some other scenarios (like exporting data to Excel, for example), sharing your experience in comments here would be highly appreciated! :)

Unknown said...

The solution seems to work at my environment,too. I'll keep on reporting about this.

I managed to rewrite your code to Windows PowerShell and it looks like this:

$site="http://localhost"
$web="/"

[System.Reflection.Assembly]::LoadFrom("C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\sysglobl.dll")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")


trap [System.Exception] {
write-host $_
continue;
}
[System.Globalization.CultureAndRegionInfoBuilder]::Unregister("nb-NO")

$reptype = [System.Globalization.CultureAndRegionModifiers]::Replacement
$lvculture = new-object -typename System.Globalization.CultureInfo -argumentlist "lv-LV"
$lvregion = new-object -typename System.Globalization.RegionInfo -argumentlist "lv"

$carib = new-object -typename System.Globalization.CultureAndRegionInfoBuilder -argumentlist "nb-NO", $reptype
$carib.LoadDataFromCultureInfo($lvculture)
$carib.LoadDataFromRegionInfo($lvregion);
$carib.GregorianDateTimeFormat.ShortDatePattern = "dd.MM.yyyy"
$carib.Register()

$spsite = new-object -typename Microsoft.Sharepoint.SPSite -argumentlist $site
$spweb = $spsite.openweb($web)
$culture = new-object -typename System.Globalization.CultureInfo -argumentlist "nb-NO"
$spweb.locale = $culture
$spweb.update()

Anonymous said...

I found a workaround to using the CultureAndRegionInfoBuilder by deriving from DateTimeControl and then overriding the OnPreRender event so that the date gets refreshed on postback. I blogged about this solution here: Custom DateTimeControl Formatting
Thanks for your suggestions!

Gold Guide for World of Warcraft said...

good post :)

Send Flowers to USA said...

Hey great stuff, thank you for sharing this useful information and i will let know my friends as well.

Anonymous said...

This is fifty percent entertaining. But then again I'm very high right now

Anonymous said...

HI all,

When wanting to implement the ISO8601 for your SP farm, and still maintaing all the week and date names in English, would it not be best to set the Eglish South africa as the default locale?

Ameer said...

nice tips. Thanks

Ameer said...

Thanks for this tip.

Siva Subramanian said...

I have a SharePoint Site Collection with regional settings changed to dd/mm/yyyy overall, all date picker controls change and I get the dd/mm/yyyy format.

I have an Enterprise Search Center in which we have the Advanced Search, where one can search for all content based on Created or Modified Date. Now, this continues to only take mm/dd/yyyy format of dates inspite of that site's regional settings being changed to dd/mm as well.

How do we change this and make all the date parameters in the Advanced Search work on dd/mm/yyyy?

Siva Subramanian said...
This comment has been removed by the author.
Unknown said...

Both sides also collectively released a 3 festivities to commemorate the tenth anniversary of cooperation
Nike Air Max Shoes
Nike Air Max 2011
Nike Air Max
Nike Air Max 2009
Nike Air Max 95
Nike Air Max 91
Nike Air Max 87
Nike Air Max 180
Nike Air Max Griffey Max 1
Nike Air Max 90
Nike Air Max 1
Nike Air Max Wildwood Supreme
Nike Air Max Turbulence
Nike Air Max Skyline
Nike Air Max Goadome
Nike Air Max Fitsole
Nike Air Max Zenyth
Nike Air Max Boots
Nike Air Max Zoom Kobe
Nike Air Max Tn
Nike Air Max LTD
Nike Air Max Presto
Nike Air Max BW
Nike Air Max 24 7
Nike Air Max 2010
Nike Air Max 2011
Abercrombie and Fitch London
Abercrombie & Fitch Clothes
Vibram Shoes
Cheap Air Max
Nike Air Max Shoes
I saw peach falling from the window in your house. Walking really happy, to ensure that all of the uncomfortable into simple

silence. I'm the only person who heard one's heart beat.

top seo forums said...

Its really an awesome post. Good concepts chosen. Excellent work done..

Funny blog site said...

As i go through your post, i found your blog quite interesting as well as informative too..

Anonymous said...

dating and learning disabilities http://loveepicentre.com/success_stories.php who is lil wayne dating

Anonymous said...

[url=http://loveepicentre.com/testimonials.php][img]http://loveepicentre.com/uploades/photos/6.jpg[/img][/url]
dating site for cheating wives [url=http://loveepicentre.com]dating for sex uk site[/url] savannah dating personal
russian dating domestically [url=http://loveepicentre.com/advice.php]matches chinese invention dating[/url] akron woman dating married men
my ex is dating someone else [url=http://loveepicentre.com]speed dating santa rosa[/url] french women dating

Anonymous said...

sex chat dating site http://loveepicentre.com/taketour.php vintage fender amplifier dating

Anonymous said...

lean process ebook http://audiobooksplanet.co.uk/Aquent-Creative-Team/m9124/ rcc dam pdf ebook free download [url=http://audiobooksplanet.co.uk/A-Schoenberg-Reader-Documents-of-a-life/p226929/]garage winifred asked mechanic ebook[/url] wheelers dental anatomy ebook

Anonymous said...

refurbished ebook reader http://audiobooksworld.co.uk/it/B-Daya-Ry/m10614/ ebook for resale [url=http://audiobooksworld.co.uk/Upham/m127602/]celtic gold ebook peter marshall[/url] define ebook

Anonymous said...

wire shark software http://buyoem.co.uk/es/product-16128/Adobe-Acrobat-8-Professional-Mac tableau software purchase contract [url=http://buyoem.co.uk/product-37136/SnowFox-Screen-Recorder-1-1]task software user information[/url] sunflower managment software
[url=http://buyoem.co.uk/it/category-200-212/Multimediale-e-Divertimenti]Multimediale e Divertimenti - Download OEM, Software Sale, OEM Software[/url] best ministry software
[url=http://buyoem.co.uk/es/category-100-112/Multimedias-y-entretenimiento?page=3][img]http://buyoem.co.uk/image/3.gif[/img][/url]

Anonymous said...

At this time it seems like Drupal is the preferred blogging
platform available right now. (from what I've read) Is that what you're using on
your blog?

Also visit my webpage :: visit saffron walden

Anonymous said...

Very nice post. I just stumbled upon your weblog
and wished to say that I've truly enjoyed surfing around your blog posts. After all I will be subscribing to your rss feed and I hope you write again very soon!

Feel free to surf to my blog ... hairdressing scissors

Anonymous said...

I write a comment when I like a article on a site or if I have something to contribute to the
conversation. Usually it is a result of the fire communicated
in the article I browsed. And after this article "How to modify date format in SharePoint".
I was actually moved enough to leave a thought :
-) I do have some questions for you if you do not mind.
Is it simply me or does it seem like some of these responses come
across like left by brain dead visitors? :-P And, if you are posting on additional online social sites,
I'd like to follow you. Could you list all of all your shared sites like your Facebook page, twitter feed, or linkedin profile?

Also visit my site ... deer antler spray

Anonymous said...

This design is wicked! You obviously know how to keep
a reader amused. Between your wit and your videos, I was almost moved to start my own
blog (well, almost...HaHa!) Excellent job. I really
enjoyed what you had to say, and more than that, how you presented it.
Too cool!

Feel free to surf to my weblog ... http://www.coenembo.com/foro/blogs/user/abduljarm

Anonymous said...

I'm impressed, I must say. Seldom do I come across a blog that's both equally educative and entertaining, and without
a doubt, you've hit the nail on the head. The issue is something that too few folks are speaking intelligently about. I am very happy I stumbled across this during my hunt for something concerning this.

My website avis 24option

Anonymous said...

I loved as much as you'll receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get bought an edginess over that you wish be delivering the following. unwell unquestionably come further formerly again since exactly the same nearly very often inside case you shield this increase.

Feel free to visit my webpage - 24option

Anonymous said...

Hey! I'm at work browsing your blog from my new iphone 4! Just wanted to say I love reading through your blog and look forward to all your posts! Keep up the excellent work!

Also visit my page: http://www.smlingrell.com/caregivinggroup/AnnettafSellshr

Anonymous said...

Normally I don't read post on blogs, however I wish to say that this write-up very pressured me to try and do it! Your writing taste has been amazed me. Thanks, very nice article.

Check out my web page binoa

Anonymous said...

Pretty! This was an extremely wonderful post.
Thanks for providing this information.

Also visit my webpage ... avis 24option

Anonymous said...

Its such as you learn my mind! You appear to grasp a lot about this, such as you wrote the e book in it or something.

I feel that you can do with some % to pressure the message house a bit, however instead of that, that is great blog. A fantastic read. I will definitely be back.

My website :: 24option

Anonymous said...

Now I am going away to do my breakfast, when having my breakfast coming yet again to read other news.


my webpage; avis binoa

Anonymous said...

Woah! I'm really loving the template/theme of this website. It's simple, yet
effective. A lot of times it's very difficult to get that "perfect balance" between usability and appearance. I must say you have done a amazing job with this. Additionally, the blog loads extremely quick for me on Firefox. Superb Blog!

My page - Cheap Nike Free Run

Anonymous said...

My spouse and I stumbled over here coming from a different page and thought I may as well check things out.

I like what I see so i am just following
you. Look forward to exploring your web page for a second time.


Take a look at my web blog fake ray ban sunglasses

Anonymous said...

I simply couldn't go away your site before suggesting that I really enjoyed the standard info a person supply on your guests? Is gonna be back often in order to investigate cross-check new posts

Here is my web-site: how to settle your credit card debt yourself

Anonymous said...

Ні theге! I κnοw thiѕ iѕ somewhаt off topic but I
ωas ωondeгing if yοu knew wherе I
could fіnԁ a сaptcha ρlugin for my comment form?

Ι'm using the same blog platform as yours and I'm having
pгoblems fіnding оne? Thanks a lοt!



my ѕіte green coffee extract dietary supplement

Anonymous said...

Have you ever considered about including a little bit more than just your articles?
I mean, what you say is fundamental and all.
But imagine if you added some great images or videos to give your posts more, "pop"!
Your content is excellent but with images and videos, this blog could definitely be one of the best in its field.
Excellent blog!

Here is my web blog :: www.tune2win.Com

Anonymous said...

Hello great blog! Does running a blog such as this require
a great deal of work? I have very little knowledge of programming however
I had been hoping to start my own blog in the near future.
Anyway, should you have any recommendations or tips for new blog owners
please share. I understand this is off topic however I simply had to ask.
Many thanks!

My site live camera

Anonymous said...

I read this paragraph completely about the difference
of newest and previous technologies, it's awesome article.

Take a look at my weblog Fassette.Com

Anonymous said...

You reallу mаke it seem reаlly еаѕy wіth
youг prеѕentation but I to find this topіc to be аctually one thing that
I believе I'd never understand. It sort of feels too complicated and very wide for me. I am taking a look forward to your next submit, I will attempt to get the hang of it!

my web blog ... cheap auto Insurance quotes

Anonymous said...

Hеy there! ӏ know thіs is somewhat οff topic but Ι was ωondеring if
you knew wheгe I coulԁ loсate a caρtсha plugin for my comment form?
I'm using the same blog platform as yours and I'm haѵing prоblemѕ finding one?

Thanks a lot!

Visit my web-site :: betfair

Anonymous said...

Have you ever thought about creating an e-book or guest authoring on other websites?
I have a blog based upon on the same subjects you
discuss and would love to have you share some stories/information.
I know my viewers would value your work. If you're even remotely interested, feel free to shoot me an e-mail.

Take a look at my web page Free live sec Cams

Anonymous said...

Excellent blog here! Also your website so much up very fast!
What web host are you the usage of? Can I get your associate hyperlink on your host?

I want my web site loaded up as quickly as yours lol

Here is my website - nude sex webcams

Anonymous said...

Wow that was odd. I just wrote an very long comment but after I clicked submit my comment didn't appear. Grrrr... well I'm not writing all that over again.

Anyway, just wanted to say wonderful blog!

my page; http://www.enstarz.com/

Anonymous said...

Nice blog here! Also your web site loads up very
fast! What web host are you using? Can I get your
affiliate link to your host? I wish my website loaded up as
fast as yours lol

Take a look at my site: prosportstv.y0.pl

Anonymous said...

dating oil rig workers http://loveepicentre.com/ bbw colombian dating
dating online rating services [url=http://loveepicentre.com/taketour/]canadian sex dating[/url] swedish dating online
old expression for dating [url=http://loveepicentre.com/faq/]xxxx milf dating[/url] winchester model 94 dating [url=http://loveepicentre.com/user/mahmood1206/]mahmood1206[/url] recommendations to internet dating

Anonymous said...

This is my first time visit at here and i am in fact impressed to read all at single place.


Feel free to visit my website ... Nike Air Max

Anonymous said...

I’m not that much of a online reader to be honest but your blogs really nice,
keep it up! I'll go ahead and bookmark your website to come back down the road. All the best

Feel free to visit my page: liveporn for free - -

Anonymous said...

Why viewers still use to read news papers when in this technological world all is available on
web?

My web blog: live porn chat Free

Anonymous said...

Appreciating the time and effort you put into your site and
in depth information you offer. It's awesome to come across a blog every once in a while that isn't the same unwanted
rehashed material. Excellent read! I've saved your site and I'm adding your RSS feeds to my Google account.


My page - pornographic movies for free

Anonymous said...

I will immediately snatch your rss feed as
I can't to find your email subscription hyperlink or e-newsletter service. Do you have any? Please permit me recognize so that I may subscribe. Thanks.

Have a look at my web blog sexi chat ()

mens ed pills said...

hey there and thank you for your info – I've definitely picked up something new from right here. I did however expertise some technical issues using this web site, since I experienced to reload the web site many times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I am complaining, but slow loading instances times will often affect your placement in google and could damage your quality score if ads and marketing with Adwords. Well I'm adding this RSS to my e-mail and can look out for a lot more of your respective exciting content. Make sure you update this again very soon.

ed treatment tablets said...

Thanks for every other informative website. The place else could I get that type of information written in such a perfect way? I have a mission that I'm simply now operating on, and I have been at the look out for such info.

impotence said...

If you are going for finest contents like I do, just pay a visit this web page every day because it provides quality contents, thanks