{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 放荡不羁爱自由 的问题《TimeZoneCode to TimeZoneInfo》','https://www.manongdao.com/q-337074.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
In our MS dynamics CRM project we created a mass-user upload batch.
The batch reads from an excel file and does a mass upload of the users.
One of the things this batch needs to set is the timezonecode.
In the excel file the timezone will be written as eg "UTC+1"
The code used by CRM seems to be the timezonecode SQL-server is using as can be found here.
What is the cleanest way of mapping these?
My ideas so far:
- Hardcode a conversion store
- Fetch the codes from CRM somehow
- Fetch the codes from SQL somehow
Currently we just implemented our own conversion class with hard coded values.
Is there a better way? Can we leverage the .net TimezoneInfo class somehow?
Update
To get all the CRM timezones we did the following:
var colSet = new ColumnSet(true);
var query = new QueryExpression(TimeZoneDefinition.EntityLogicalName) { ColumnSet = colSet};
var timeZoneDefs = service.RetrieveMultiple(query).Entities.Select(tz => tz.ToEntity<TimeZoneDefinition>());
But it seems the only properties filled are Id, Code, StandardName and UserInterfaceName. It seems only the UI name contains the offset we're looking for.
Is there any way to ensure the Bias property is loaded?
Here is the code that I use to get a user's TimeZoneInfo. It retrieves the TimeZoneDefinition from CRM based on the name, but I believe you can look it up by the Bias matching the UTC offset from your excel file.
Edit - Bias is null
This could just be our on prem version of CRM, but this is what it is currently populated in CRM for any time zone that is -5, -6,-7, or -8.
.
This would make the bias lookup null & void.
On a side note, 99% of our users are on Eastern Time, but we have a few in California, and I haven't heard of any issues as of yet. But now I'm wondering if we bothered to test this before and after DST...
In case you should need a list of all CRM timezones and their relative SQL identifier: We wrote a little program to loop over all the options in CRM and output the name, semicolon, sql code.