In MS-CRM database as we know end user can set the timezone of his choice for himself / herself. And after that all the data that end user sees on CRM UI would be displayed in the timezone that he/she has opted for.
Now, the question is where does CRM stores the TIMEZONE related information? hence comes the TimeZoneDefinitionBase table to rescue.
It holds the info such as
- TimeZoneCode - Unique Integer value representing each timezone
- UserInterfaceName - Displays info in-terms of UTC +/- Hrs
- StatndardName - Gives away the global standard name for the TimeZone that everyone can understand
Alright.. So CRM does store that information but where is the association between this timezone and the CRM user is stored ?
There is a table UserSettingsBase which holds the relation between the CRM user and the timezone that user has chosen. This table has many columns but the ones that are required for this blog post are
- Systemuserid - GUID of the CRM user
- TimeZoneCode - Timezonecode that CRM user has subscribed for
This paves the way for writing the SQL query which is the sole reason of this blog entry..duhhh..
SQL Query :
SELECT
SU.FullName AS UserName
,SU.DomainName AS UserId
,TD.UserInterfaceName AS UserTimeZone
,TD.StandardName AS TimezoneName
,US.DateFormatString AS DateFormat
,US.TimeFormatString AS TimeFormat
FROM TimeZoneDefinitionBase TD
INNER JOIN UserSettingsBase US
ON TD.TimeZoneCode = US.TimeZoneCode
INNER JOIN SystemUserBase SU
ON SU.SystemUserId = US.SystemUserId
WHERE SU.IsDisabled = 0
ORDER BY UserName
As you must have noticed that I've used Systemuserbase to fetch the user information and to restrict result set for ACTIVE (enabled) CRM users only.
No comments:
Post a Comment