Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

DateTime field attribute DataOnly adds time to database

  • 1.  DateTime field attribute DataOnly adds time to database

    Posted 12 days ago
    Hello,

    We recently changed the DateTime Behavior attribute from User Local to DateOnly for static date fields (Date of Birth, Date of Death) so that all users in all time zones see the same date.

    From our research, we understood that, in the database, the time portion of the DateTime fieldwould continue to be 00:00:00.000. Since the attribute change, it is actually converting the time.

    For example: A field with '02/05/2019', saved in EST is displaying properly on the screen for all users in all zones but querying the database (i.e. SQL Report) shows the following in the database:

    2019-02-04 19:00:00.000

    We were expecting 2019-02-05 00:00:00.000

    Dynamics 365 On-premise, 8.2.2.2012

    Has anyone else experienced this?  This can be a huge problem moving forward.

    Thanks.

    ------------------------------
    Andrea Melee
    CFMWS
    Ottawa ON
    ------------------------------


  • 2.  RE: DateTime field attribute DataOnly adds time to database

    SILVER CONTRIBUTOR
    Posted 11 days ago
    Hi Andrea, this is a known effect of changing the behavior.
    You could probably be helped by using the DateTime Behavior Utility for XrmToolBox to adjust the underlying data.
    https://www.xrmtoolbox.com/plugins/Innofactor.XTB.DateTimeBehaviorUtility/

    I haven't used it in quite some time, but it was created for the sole purpose of handling this type of problems.

    ------------------------------
    Jonas Rapp
    MVP, Enterprise Architect
    Avanade
    Täby
    ------------------------------



  • 3.  RE: DateTime field attribute DataOnly adds time to database

    TOP CONTRIBUTOR
    Posted 10 days ago
    Andrea

    Is you SQL report using the view or the base table? If base table, switch it to the view as it has the mechanisms to properly display the date based on the user settings.

    Cheers!


    ------------------------------
    Rex Kenley Tan, MCP
    Akron OH
    https://www.youracclaim.com/users/rex-kenley-tan

    *Always be CURRENT with JavaScript & C#, NEVER be obsolete.

    DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
    ------------------------------



  • 4.  RE: DateTime field attribute DataOnly adds time to database

    Posted 10 days ago
    Thanks Rex for the hint to check views vs tables.  I actually found this as the result.  It seems CRMAF_Filtered is returning the adjusted vaules (which we do not want).

    Here dateinitiated has 'DateOnly' Behaviour, CreatedOn is the OOTB field which has not been changed.


    Select CRMAF_Filterednew_tbl.new_dateinitiated as dateinitiated, CRMAF_Filterednew_tbl.createdon as CreatedOn

    from  CRMAF_Filterednew_tbl

     

    dateinitiated                               CreatedOn

    2016-07-07 00:00:00.000                2016-07-07 11:52:00.000

    2019-02-04 19:00:00.000                2019-02-05 12:25:30.000

     ------------------------------------------- 

    Select new_tbl.new_dateinitiated as dateinitiated, new_tbl.createdon as CreatedOn

    from new_tbl 

    dateinitiated                               CreatedOn

    2016-07-07 04:00:00.000                2016-07-07 15:52:00.000

    2019-02-05 00:00:00.000                2019-02-05 17:25:30.000


    ------------------------------------------- 

    Select new_tblBase.new_dateinitiated as dateinitiated, new_tblBase.createdon as CreatedOn

    from new_tblBase 

    dateinitiated                               CreatedOn

    2016-07-07 04:00:00.000                2016-07-07 15:52:00.000

    2019-02-05 00:00:00.000                2019-02-05 17:25:30.000



    Is there another way to filter the results for a report if CRMAF_Filtered is returning wrong data?

    Thanks

    ------------------------------
    Andrea Melee
    CFMWS
    Ottawa ON
    ------------------------------



  • 5.  RE: DateTime field attribute DataOnly adds time to database

    TOP CONTRIBUTOR
    Posted 10 days ago

    Andrea

    Off the top of my head, I can think off 2 solutions

    1.) Since CRMAF is partially controlled by the user's time zone setting. Create a "service view" account that has a time zone setting that would return the correct value. Use the "service view" account to impersonate every time this report is loaded.

    2.) Create an inner join that returns the CRMAF columns except datetime and the datetime columns from the tables.

    Cheers!



    ------------------------------
    Rex Kenley Tan, MCP
    Akron OH
    https://www.youracclaim.com/users/rex-kenley-tan

    *Always be CURRENT with JavaScript & C#, NEVER be obsolete.

    DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
    ------------------------------



  • 6.  RE: DateTime field attribute DataOnly adds time to database

    Posted 9 days ago
    Thanks Rex.

    What I actually did was add 8hrs to the date fields for the reports that need CMRAF. As the users for these reports are Headquarters based, adding this time will always be accurate now.  We will have to keep this in mind for future enhancements when multiple time zones come into play.  Perhaps an impersonation account may be useful.

    As another note for anyone else noticing this, we also have some reports being generated via SSIS using Kingswaysoft adapter.  Dates were also miscalculated on those as well.  In the adapter, we changed "Output Timezone" from "Adjust to timezone of Connection User" to "UTC(Default)" and the dates now are all matching in CRM and the reports.





    ------------------------------
    Andrea Melee
    CFMWS
    Ottawa ON
    ------------------------------