Microsoft Dynamics CRM Time zones

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:

·         Joe is in the New York office and creates an appointment in CRM with a scheduled start of '26 Nov 2001 13:00'.

·         Karen is in the Paris office and opens up the same appointment created by Joe, and observes that the start time is '26 Nov 2011 19:00'.

1. 26 Nov 2001 13:00 – value entered by Joe in New York office as scheduled start date/time. New York is in EST (UTC-5) – i.e. UTC minus 5 hours
2. 26 Nov 2011 18:00 – value stored in the Database by CRM – converted to UTC date/time by adding 5 hours.
3. 26 Nov 2011 19:00 – value viewed by Karen in the Paris office – CRM converts from UTC to Karen's local time of UTC+1 by adding 1 hour.

The following SQL shows this example in action:

PRINT 'Non-Daylight Saving Test'

DECLARE @utc datetime = '2001-11-26 18:00:00'

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 

-- Entered as 2001-11-26 13:00:00

PRINT 'New York (GMT-05:00)+1  ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

PRINT 'UTC ' + CONVERT(nvarchar(30),@utc,120)

--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris

PRINT 'Paris (GMT+1)+1 ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)

Daylight Saving adjustments

The following scenario is similar to the above except, the date is now in the summer, and subject to daylight saving adjustments.

 Read this article for more info on daylight saving adjustments.

Joe is in the New York office and creates an appointment in CRM recorded as '26 June 2001 13:00'. Karen is in the Paris office and opens up the same appointment created by Joe, and observes that the start time is '26 June 2011 19:00'.

1. 26 June 2001 13:00 – value entered by Joe in New York office as scheduled start date/time. New York is in EST (UTC-5) – but they are also on Daylight saving which is +1 hour.
2. 26 June 2011 17:00 – value stored in the Database by CRM – converted to UTC date/time by adding 4 hours – less one hour due to the daylight saving.
3. 26 June 2011 19:00 – value viewed by Karen in the Paris office – CRM converts from UTC to Karen's local time of UTC+1 by adding 1 hour and then another hour for daylight saving adjustment.

Daylight saving adjustments are based upon the date entered, and not by the current date time at time of entry. So when a date of 26 June is entered in on the 26 November, the daylight saving adjustment is still made. This ensures that datetimes are always constant in the same time zone – you don’twant the time of an appointment to change depending on what time of year you viewed the record.

The following SQL shows this example in action:

PRINT 'Daylight Saving Test'

DECLARE @utc datetime = '2001-06-26 17:00:00'

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 

-- Entered as 2001-06-26 13:00:00

PRINT 'New York (GMT-05:00)+1  ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

PRINT 'UTC  ' + CONVERT(nvarchar(30),@utc,120)

--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris

PRINT 'Paris (GMT+1)+1 ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)

Dynamics CRM *always* stores a time element with dates

Dynamics CRM doesn't support storing just dates, those will always have a time element even when it's not displayed in the User Interface or exports. This can cause issue for dates such as 'date of birth' – consider the following:

1. 26 Nov 2011– Date of birth entered by Karen in the Paris office.
2. 26 Nov 2011 00:00 - Date of birth sent to the Web Server by the form submit.
3. Note that the time element is set to zero-hundred hours if a date time field is configured to only show the date element.
4. 25 Nov 2011 23:00 - Date stored in the database converted to UTC by subtracting 1 hour – Since Karen's local time zone is in UTC+1.
5. 25 Nov 2011 – Date shown to Bob who is in London on GTM (UTC+0)

So a date of birth entered correctly by Karen in Paris shows the wrong date to Bob in London due to the time zone UTC conversion.

The following SQL shows this example in action:

PRINT 'Date of birth test'

DECLARE @utc datetime = '2001-11-25 23:00:00'

--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris

-- Entered as 2001-11-26 (sent as 2001-11-26 00:00:00)

PRINT 'Paris (GMT+1) ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)

PRINT 'UTC' + CONVERT(nvarchar(30),@utc,120)

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 

PRINT 'New York (GMT-05:00)       ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

Absolute Date solutions

There are a number of solutions to this absolute date issue:

1.     Adjust the date/time at point of entry (JavaScript or PlugIn) and convert to mid-day (12:00) so that any time conversion will not move it over the date line. This will only work when you don't have any offices that are more than 12 hours apart.

2.     Write a plugin that intercepts any Retrieve/RetrieveMultiple messages and adjusts the time to correct for the time zone offset. This will only work when a date is displayed in a Form or Data Grid – it does not work with SQL based reports or when dates are compared within an advanced find search criteria.

3.     Store the date of birth as a string or 3 options sets for year, month and day – this is ithe only way to completely avoid the time zone conversion issue for absolute date fields.

You can see the 12:00 date correction in action here:

1. 26 Nov 2011– Date of birth entered by Karen in the Paris office.
2. 26 Nov 2011 12:00 - Date of birth sent to the Web Server by the form submit (or adjusted in a PlugIn pipeline). 
3. 26 Nov 2011 11:00 - Date stored in the database converted to UTC by subtracting 1 hour – Since Karen's local time zone is in UTC+1.
4. 26 Nov 2011 – Date shown to Bob who is in London on GTM (UTC+0) Correct!

PRINT 'Date of birth test ( 12:00 corrected)'

DECLARE @utc datetime = '2001-11-26 11:00:00'

--(GMT+01:00) Brussels, Copenhagen, Madrid, Paris

-- Entered as 2001-11-26 (sent as 2001-11-26 12:00:00)

PRINT 'Paris (GMT+1) ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,105),120)

PRINT 'UTC ' + CONVERT(nvarchar(30),@utc,120)

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 

PRINT 'New York (GMT-05:00) ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

The downside of this is that when two offices are in time zones more than 12 hours apart, the conversion will still take the date over the date line, and will not show the birth date correctly. At that point, your only option is a text date field.

PRINT 'Date of birth test ( 12:00 corrected - timezone problem)'

DECLARE @utc datetime = '2001-11-26 17:00:00'

--(GMT-05:00)+1 Eastern Time (US & Canada) New York EST 

PRINT 'New York (GMT-05:00) ' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,35),120)

PRINT 'UTC ' + CONVERT(nvarchar(30),@utc,120)

--Fiji (GMT+12)

PRINT 'Fiji (GMT+12)' + CONVERT(nvarchar(30),dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,285),120)

SDK Web Services DateTime Gotcha

The SDK Web Services can accept:

·         either a local datetime or a UTC datetime when performing a create/update,

·         but will always return a UTC date on Retrieve/RetrieveMultiple.

For this reason, be very careful when you retrieve a value, update it and then send it back.

Using the SOAP endpoint, you will always get a UTC date,. To get into a local date on a client (assuming that the client has their datetime set correctly) use DateTime.ToLocalTime, . When you can't guarantee the time zone settings then use the LocalTimeFromUtcTimeRequest.

Contact contact = (from c in ctx.CreateQuery()

                where c.LastName == lastName

                    select c).FirstOrDefault();

Console.WriteLine("UTC Time " + contact.BirthDate.ToString());

Console.WriteLine("Local Time (Converted on Client) "  + contact.BirthDate.Value.ToLocalTime().ToString());

 LocalTimeFromUtcTimeRequest convert = new LocalTimeFromUtcTimeRequest

{

    UtcTime = contact.BirthDate.Value,

    TimeZoneCode = 85 // Timezone of user

};

LocalTimeFromUtcTimeResponse response = (LocalTimeFromUtcTimeResponse)_service.Execute(convert);

Console.WriteLine("Local Time (Converted on Server) " + response.LocalTime.ToString());

To update the date, ensure to specify whether it's a local datetime or a UTC datetime.

newContact.BirthDate = new DateTime(2001, 06, 21, 0, 0, 0, DateTimeKind.Utc);

// or

newContact.BirthDate = new DateTime(2001, 06, 21, 0, 0, 0, DateTimeKind.Local);

When using the REST endpoint, set a UTC date using the following format:

<d:BirthDate m:type="Edm.DateTime">2001-06-20T23:00:00Z</d:BirthDate>

 To set a local date time, where it will be converted to UTC on the server simply omit the trailing Z.

(The 'Z' is from the Navy and Aviation's use of 'Zulu' time which is equivalent to UTC (but shorter!))

<d:BirthDate m:type="Edm.DateTime">2001-06-20T23:00:00Z</d:BirthDate>

GMT 'Time-bomb' Gotcha

The problem with the UK is that for half the year, the time zone is the same as UTC (GMT+0) – which means date time conversion issues are often not spotted when the development is taking place i.e. when British Summer Time (BST) Daylight saving is not in effect – this is because any dates entered into CRM are stored as UTC which is the same dat. Aas soon as Daylight saving comes into effect, the problem is then spotted (hopefully!) because dates are an hour out in reports and integrations with other systems because the utc date is being read from the database or SDK and is not converted to GMT.

Key Points:

So in summary, here are the key points to remember:

1. Date/Times are always stored in the MSCRM database as UTC dates.
2. When querying the Base table or views for an entity (e.g. ContactBase or Contact), the dates will be UTC.
   E.g. the following dates will be in UTC
   Select birthdate From ContactBase
   Select birthdate form Contact
3. When querying the Filtered Views, the dates will be in the local time specified in the current user's settings. There is another field provided that is suffixed by UTC that provides the raw date without any conversion.
   E.g. The first date will be local time zone correct, and the second field will always be utc
   Select birthdate,birthdateutc from FilteredContact
4. When sending a date/time in SOAP SDK Message (e.g. create/update), the date will default to local time if you use a DateTime.Parse – and to send a UTC time, set the DateTimeKind to UTC.
5. Important: When querying the SOAP SDK, any date/times will be returned as UTC dates, and must be converted to local time using DateTime.ToLocalTime when you know that the locale of the current process is set correctly, or the LocalTimeFromUtcTimeRequest SDK message.
6. When importing &updating data via the Import Wizard, date/times must be specified in the local date of the user who is importing them.

Date time offset

Although the DateTimeOffset structure provides a greater degree of time zone awareness than the DateTime structure, DateTime parameters are used more commonly in method calls. Because of this, the ability to convert DateTimeOffset values to DateTime values and vice versa is particularly important. See https://msdn.microsoft.com/en-us/library/bb546101(v=vs.110).aspx

Fiscal Year in CRM

Here is the query using C#,  using either LINQ or Late Bound;

//Using LINQ

 private void RetrieveOrganizationFiscalYear()

{

   _service = new XrmContext();

   XrmContext xrmContext = new XrmContext();

   var OrganizationSettings = from os in xrmContext.OrganizationSet

   select new Organization

 {

FiscalCalendarStart = os.FiscalCalendarStart,

 };

 DateTime dtStartFiscalYear = OrganizationSettings.First().FiscalCalendarStart.Value;

 //this is the date of start fiscal year

 }

 //Using Late Bound

 private void RetrieveOrganizationFiscalYearLateBound(IOrganizationService _service)

 {

  //Retrieve organization

 Entity enOrganization = new Entity("organization");

EntityCollection ecOrganizations = new EntityCollection();

DateTime dtStartFiscal = new DateTime();

QueryExpression qx = new QueryExpression();

 qx.EntityName = "organization";

 qx.ColumnSet.AllColumns = true;

 ecOrganizations = _service.RetrieveMultiple(qx);

 if (ecOrganizations.Entities.Count > 0)

  {

  enOrganization = ecOrganizations.Entities[0];

  dtStartFiscal = enOrganization.GetAttributeValue<DateTime>("fiscalcalendarstart");

 //this is the date of start fiscal year

}

 } 

Behavior and format of the date and time attribute

When you have users and offices around the world, it is important to properly represent date and time values in multiple time zones. The DateTimeAttributeMetadata class is used to define and manage attributes of type DateTime in CRM. Use the DateTimeAttributeMetadata.DateTimeBehavior property to define whether to store date and time values with or without time zone information, and use the DateTimeAttributeMetadata.Format property to specify the display format of these attributes.

The DateTimeAttributeMetadata.DateTimeBehavior property is available only if you are using Microsoft Dynamics CRM Online 2015 Update 1 or Microsoft Dynamics CRM 2016 (on-premises). Also, all date and time attributes in CRM Online 2015 Update 1 and CRM 2016 (on-premises) now support values as early as 1/1/1753 12:00 AM.

For CRM versions earlier than CRM Online 2015 Update 1 and CRM 2016 (on-premises), you cannot define the behavior of the date and time values. By default, the date and time values are stored as UserLocal behavior.

https://msdn.microsoft.com/en-us/library/dn996866.aspx

If you are working with Silverlight web resource and you need to get current crm user date time format then you can query usersettings entity and get datetime format setting for user. You can use below code for the same:

    public void RetrieveUserSettingRecord(Guid _UserID)
        {
         try
            {
                this._d.BeginInvoke(delegate()
                {
                    EntityReference EntityID = new EntityReference();
                    //the guid of the entity you want to retrieve
                    EntityID.Id = _UserID;
                    //the name of the entity type you want to retrieve
                    EntityID.LogicalName = "usersettings";
                    //define request type
                    OrganizationRequest request = new OrganizationRequest() { RequestName = "Retrieve" };
                    request["Target"] = EntityID;
                    ColumnSet columns = new ColumnSet();
                    columns.Columns = new System.Collections.ObjectModel.ObservableCollection<string>(new string[] { "dateformatstring", "timeformatstring" });
                    request["ColumnSet"] = columns;
                    IOrganizationService service = SilverlightUtility.GetSoapService();
                    //send the async request and specify it's callback
                    service.BeginExecute(request, new AsyncCallback(RetrieveCurrentUserSetting), service);

                });

            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        private void RetrieveCurrentUserSetting(IAsyncResult result)
        {
            try{
           
                this._d.BeginInvoke(delegate()
                {
                    OrganizationResponse Response = ((IOrganizationService)result.AsyncState).EndExecute(result);
                    SilverCrmSoap.CrmSdk.Entity _SystemUser = (SilverCrmSoap.CrmSdk.Entity)Response["Entity"];
                    if (_SystemUser.GetAttributeValue<string>("dateformatstring") != null)
                    {
                        string DateformatString = _SystemUser.GetAttributeValue<string>("dateformatstring");
                    }
                    if (_SystemUser.GetAttributeValue<string>("timeformatstring") != null)
                    {
                        string TimeFormatString = _SystemUser.GetAttributeValue<string>("timeformatstring");

                    }
                });
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }