locked
strange issue with date field RRS feed

  • Question

  • CRM2013

    I am having a strange problem with a date only field in a custom entity.

    I have imported records to this entity and when viewing a record the date on the form is showing 16/07/2014 which is correct.

    If i run a SQL select query on that record, the date is showing as 15/07/2014 23:00:00

    I have tried creating a record manually within CRM and setting the date via the date picker to 16/07/2014 and again when querying the record under SQL i get 15/07/2014 23:00:00

    I have read a few threads that mention regional setting, however in this test environment all user's are set to the same time zone

    Both the Servers that have the CRM and SQL servers on are both set with the same time zone

    The reason I need to query  CRM from SQL is because we import hundreds of customer reports and part of the query to identify which report is for which customer is based on the date field

    I can only assume that there is a setting somewhere in CRM that sets a default time zone. Can anyone shed any light on this ?


    Dont ask me .. i dont know

    Thursday, July 17, 2014 9:53 AM

Answers

  • Hi Pete,

    If you're querying directly the SQL Server database, you'll always get the dates in UTC, since that's how it's stored in the database. CRM converts the date time to your region when viewed from the front end.


    Admin QuikView Solution for CRM 2013

    • Marked as answer by Pete Newman Thursday, July 24, 2014 9:28 AM
    Thursday, July 17, 2014 9:55 AM

All replies

  • Hi Pete,

    If you're querying directly the SQL Server database, you'll always get the dates in UTC, since that's how it's stored in the database. CRM converts the date time to your region when viewed from the front end.


    Admin QuikView Solution for CRM 2013

    • Marked as answer by Pete Newman Thursday, July 24, 2014 9:28 AM
    Thursday, July 17, 2014 9:55 AM
  • HI,

    I understand that, but what I don't get is this test environment is in the same location, all machines are set to the same regional settings.

    When importing the data it is for instance 16/07/2014 00:00:00

    which is what i would expect to get back out when running a SQL query.

    What I'm not understanding is if all machines and servers are the same time zones / regional setting, and all CRM users are on the same settings, why would CRM be converting it. This is what makes me think there is a setting somewhere I have missed within CRM.


    Dont ask me .. i dont know

    Thursday, July 17, 2014 10:02 AM