Things to be consider while working with Date and Time related implementations in MS CRM

Recently we faced a lot of challenges while resolving date time related attributes issues in the one of our project MS CRM 2016 project.

Did some search on different forums and MSDN we found that, if we take care of the things which are recommended and different behavioral options provided by MS CRM for date time related functionalities, surely we will be confident about the implementations and avoid the iterative support issues in future.

Here we trying to list out few important points which developer should aware very well before implementing the date time related functionalities-

1. CRM DateTime always comes with time component. There is a setting in creating a DateTime field that defines date only, but all that does is default the time to midnight local time
2. DateTime is saved in database as UTC time.

3.       DateTime in CRM UI is always shown based on user’s local time zone. This is true even if UI is only showing the date component. This leads to following effect:

·         If user in CST enters  contact’s birthdate as 2/2/1980, this is saved as 2/2/1980 05:00 in DB

·         If user in PST views the contact record, he/she will see the birthdate as 2/1/1980 (because local time for the user will be 2/1/1980 22:00)

4.       DateTime retrieved through CRM Web Services is always UTC time

5.       DateTime set through CRM Web Services is user’s local time zone by default

·         Note that extra care must be taken to understand whether the call is done with the calling user or a service user

6.       DateTime set through CRM Web Services can be defined to be UTC instead

7. DateTime queried directly from SQL table or base view returns UTC
8. DateTime queried from filtered view returns users local time

Also we have found below mentioned valuable help related to the “Behavior and format of the date and time attribute in Microsoft Dynamics CRM” -

Sr. No.	Member name and value	Description
1	UserLocal	·         Stores the date and time value as UTC value in the system. ·         The retrieve operation returns the UTC value. ·         The update operation converts the UTC value to the current user’s time zone value, and then stores the updated value as is or as the equivalent UTC value depending on the kind (DateTimeKind) of the value specified for update. If the specified value is of UTC kind, it’s stored as is. Otherwise, the UTC-equivalent value is stored. ·         Retrieving the formatted value converts from UTC to the user’s current time zone based on the time zone and locale setting of the user. ·         For the OData endpoint, the attribute is exposed as DateTimeOffset. ·         This behavior is used for system attributes like CreatedOn and ModifiedOn, and cannot be changed. You should use this behavior for custom attributes where you want to store date and time values with the time zone information.
2	DateOnly	Stores the actual date value with the time value as 12:00 AM (00:00:00) in the system. For the retrieve and update operations, no time zone conversion is performed, and the time value is always 12 AM (00:00:00). Retrieving the formatted value displays the date value without any time zone conversion. For the OData endpoint, the attribute is exposed as DateTimeOffset. This behavior should be used for custom attributes that store birthdays and anniversaries, where the time information is not required.
3	TimeZoneIndependent	Stores the actual date and time values in the system regardless of the user time zone. For the retrieve and update operations, no time zone conversion is performed, and actual date and time values are returned and updated respectively in the system regardless of the user time zone. Retrieving the formatted value displays the date and time value (without any time zone conversion) based on the format as specified by the current user’s time zone and locale setting. For the OData endpoint, the attribute is exposed as DateTimeOffset. This behavior should be used for attributes that store information such as check in and check out time for hotels.

·         Note:

·          

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

·         For Dynamics 365 versions earlier than CRM Online 2015 Update 1 and Dynamics 365 (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 as described later in this topic.

·          

Reference:

https://msdn.microsoft.com/en-in/library/dn996866.aspx#SpecifyBehavior