CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

(1) Share

Report

Posted on by rskadk1

886

Hi,

We are aware that getting the right time from the database in CRM can be triggy. But we need to get the right time for the user and send it to our ERP (In fact we have created a timezone field in the business unit - since the it's easier to maintain). We are primary gonna use it for integration to ERP.

We are aware that all date/time fields are saved as UTC in the database. So based upon the Owning business unit, we need to calculate the right time. We found that ex. GMT +1 timezone selection in CRM is equal to 105. We found some exambles on how to convert this to the right offset based upon the UTC time in the database.

However we are a Little unsure regarding the daylight saving time. IN CRM i can only select GMT +1 for Denmark (Copenhagen). But currently due daylight savings, we have an offset of +2 hours. How are CRM handling this? Ex. for the previous examble we found the value 105 based upon GMT+1 slection. How does CRM now if we use daylight savings or not based upon the selection?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Emre GULCAN 2,379 on at

Like (0)

Report

RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

Hi,

If you use directly SQL query to select data, you can find your user's TimeZone information on "dbo.UserSettings" table, and there is a convert function inside MS CRM database called "dbo.fn_UTCToTzCodeSpecificLocalTime(datetime, timezonecode)"

If you use MS CRM SDK (c#) please look at https://msdn.microsoft.com/en-us/library/gg309411(v=crm.8).aspx , you can find more timezone methods

If you need quick solution you can use XrmLibrary 's TimezoneHelper methods, you can download it from NuGet

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

@Brad Sprigg: Works perfectly. Thank you.

Was this reply helpful? Yes No
Inogic 647 on at

Like (0)

Report

RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

Hello,

CRM stores all datetime values in UTC format in database and displays in CRM in local Timezone. To handle timezone issue we can convert UTC datetime back to the user’s timezone.

To handle the Timezone issue you can follow below link.

inogic.com/.../how-to-convert-the-datetime-in-local-and-utc-date-time-format

Was this reply helpful? Yes No
Suggested answer

Brad Sprigg 985 on at

Like (1)

Report
RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

Here is some code I used recently to do this, the key is the TimeZoneInfo object used once you have determined the user's timezone

public DateTime ConvertCrmDateToUserDate(IOrganizationService service, Guid userGuid, DateTime inputDate) { //replace userid with id of user Entity userSettings = service.Retrieve("usersettings", userGuid, new ColumnSet("timezonecode")); //timezonecode for UTC is 85 int timeZoneCode = 85; //retrieving timezonecode from usersetting if ((userSettings != null) && (userSettings["timezonecode"] != null)) { timeZoneCode = (int)userSettings["timezonecode"]; } //retrieving standard name var qe = new QueryExpression("timezonedefinition"); qe.ColumnSet = new ColumnSet("standardname"); qe.Criteria.AddCondition("timezonecode", ConditionOperator.Equal, timeZoneCode); EntityCollection TimeZoneDef = service.RetrieveMultiple(qe); TimeZoneInfo userTimeZone = null; if (TimeZoneDef.Entities.Count == 1) { String timezonename = TimeZoneDef.Entities[0]["standardname"].ToString(); userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(timezonename); } //converting date from UTC to user time zone DateTime cstTime = TimeZoneInfo.ConvertTimeFromUtc(inputDate, userTimeZone); return cstTime; }

Credit to the below site for this code

scaleablesolutions.com/convert-date-from-utc-to-user-time-zone-using-timezoneinfo-class

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

CRM doesnt hold this value you need to take the users timezone and handle it according to global timezone rules/dates

Was this reply helpful? Yes No
rskadk1 886 on at

Like (0)

Report

RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

Do you mind explaining it more? There is no place in the user setting i can specify daylight savings? So how does "CRM Knows" the correct time? I can only specify ex. GMT +1 (Copenhagen) in the user settings..?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: CRM, timezones and daylight savings - How to get the right time based on UTC in the database?

stackoverflow.com/.../dynamics-crm-daylight-savings-time

msdn.microsoft.com/.../bb930195.aspx

Using the sdk your can pull the time values in the users time.

Was this reply helpful? Yes No