Microsoft D365 Tips

Time Zones Dynamics CRM stored Date/Time fields in the database as a SQL datetime field that is always converted to a UTC date.  Each user has a Time Zone Code associated with their user settings. ·          To list all the available TimeZoneCodes use the following query against the MSCRM database: SELECT TimeZoneCode, UserInterfaceName FROM TimeZoneDefinition order by UserInterfaceName ·          To list all user's selected Time Zone Code use: Select SystemUserId, TimeZoneCode from UserSettings There functions available in the MSCRM database for converting to and from UTC to local dates. The following function accepts a utc date and converts it to a local date based on the time zone code matching those in TimeZoneDefinition. dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,@timezonecode) Dates are stored as UTC Consider the following: ·        ...

Dynamics CRM uses FetchXML queries. FetchXML is a proprietary query language for Dynamics CRM. Developers with a SQL background, want to find the SQL query into which the FetchXML translates. One way is to understand the FetchXML syntax, and you can also get the SQL statement with SQL Tracing to find all the SQL queries that get executed in the background when running CRM application. Note: SQL Tracing can only be executed for CRM On-Premise systems. Take an example where we have a below Fetch XML based query that is generated using advanced find. < fetch version = "1.0" output-format = "xml-platform" mapping = "logical" distinct = "false" > < entity name = "account" > < attribute name = "name" /> < attribute name = "primarycontactid" /> < attribute name = "telephone1″ /> <attribute name=" accountid " /> < order attribute = ...