For any Dynamics 365 developer, at one point in time they might encounter issues with the datetime fields in CRM. The challenge always comes when we working on backend, because there is always a confusion in the mind whether the date time value is UTC or local time.
When working in reports, fetch-xml dataset have two values for each datetime types, the field name with just the attribute name (e.g. createdon) is the formatted value with time zone added, another one contains the UTC time which is attributenameValue (e.g. createdonValue). Formatting the datetime field in the report using the formatted value i.e. createdon will have issues as it will have issue converting the string value to datetime. In this cases, we need to use the UTC datetime field value, but the problem is we need to localize the date in order to show the data correctly in the report.
Lets see how we can achieve converting the UTC datetime to Local Time of the CRM. First thing is to reference the crm utility assembly in the report property.
Step 1: Navigate to the report properties.
Step 2: Click on Add button under the Add or remove assemblies section. Click on the three ellipses to choose the “Microsoft.Crm.Reporting.RdlHelper” assembly and choose that. This assembly is part of the BIDS installation that you carry out during the development environment setup. Then click OK.
The report will work even if you reference the older version of the assembly like 126.96.36.199. The only difference is if you don’t reference the correct version that is installed in your local machine, the report won’t render locally in Visual Studio. It will only work when you import in to CRM.
Step 3: Now we can able to use the custom function from the assembly that we referred in the previous step. Below is the formula to convert the datetime from Utc to Local Time.
Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime( Fields!createdonValue.Value, Parameters!CRM_UserTimeZoneName.Value)
In the above code, instead of hardcode the time zone string, use the hidden parameter field to get the time zone value.
4 thoughts on “SSRS convert UTC datetime value to local datetime”
I did as above, it was working for US users but started failing for UK users. any idea?
Chandra, thanks for the question. I think there are few things that might affect this setup. Firstly check the datetime attribute of the fields if they are of two different fields that you are using. It it is of the same field and you are trying for two different time zones then help me check what is the fill time zone text that you have entered in for the UK. Let me know if you need more help.
it is same field and for UK users the time zone is (GMT-11:00) Coordinated Universal Time-11 and DateFormat is English(United Kingdom) on the CRM user personal settings
Please ignore my last message. I found the issue is with the format of sort expression. it is corrected and works now. thank you! thank you for the quick response and time.