locked
CRM Date Time Format wrong RRS feed

  • Question

  • Hello @all,

    I have Date Time Problem. I have searched in the different forums for that issue, but I don' t found a solution for that issue.

    The CRM/Database Server are both in the same TimeZone. 

    But if I add a new entry in the quote entity into the field requestdeliveryby the date will be saved wrong. The Date Time Control Field shows the right Date but in the Data Base a wrong date is saved. Always the day before and the time will set to 23:00:00.

    Example:
    Tuesday 2. March will be saved in the database like this 2010-03-01 23:00:00.000 the time is always set to 23:00:00

    Actually the CRM-server has the UR8 installed.

    What can I do to avoid this failure?

    Regards, 

    Andreas 

    Monday, March 1, 2010 1:54 PM

Answers

  • Hi, Andres. There are no error. Datetime fields are saved in UTC format.

    In your reports you should use queries like

    Select dbo.fn_UTCToLocalTime(<Your DB Field>) ...

    instead of

    Select <Your DB Field> ...

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Monday, March 1, 2010 2:08 PM
    Moderator
  • I guess your in a time zone that is GMT+1?  If so be aware of UTC time 

    This article  touches on it.
    http://blogs.msdn.com/crm/archive/2007/05/22/date-time-values-in-microsoft-dynamics-crm.aspx

    @_Simon_Jackson http://www.simonjackson.info/ MBCS MCBMSS MCBMSP MCSD MCDBA MCAD MCSA
    Monday, March 1, 2010 2:11 PM

All replies

  • Hi, Andres. There are no error. Datetime fields are saved in UTC format.

    In your reports you should use queries like

    Select dbo.fn_UTCToLocalTime(<Your DB Field>) ...

    instead of

    Select <Your DB Field> ...

    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный)free countersLocations of visitors to this page
    Monday, March 1, 2010 2:08 PM
    Moderator
  • I guess your in a time zone that is GMT+1?  If so be aware of UTC time 

    This article  touches on it.
    http://blogs.msdn.com/crm/archive/2007/05/22/date-time-values-in-microsoft-dynamics-crm.aspx

    @_Simon_Jackson http://www.simonjackson.info/ MBCS MCBMSS MCBMSP MCSD MCDBA MCAD MCSA
    Monday, March 1, 2010 2:11 PM
  • Thanks for your solution.

    It is logical but my problem is the data delivery to an external CRM Tool. In this Tool the Data will be export to Office Word and there is the field wrong.

    I have to contact the producer of the interface.

    Thanks for your help.
    Monday, March 1, 2010 2:52 PM
  • Hi,

    I am having a problem when I try to use the fn_UTCToLocalTime function.  I am using it in conjuction with reports and SSAS data cubes but the security is preventing it from using it.  I normally use the sa user or the service account user but both of these do not seem to be able to access the function.  How do I fix the security so that any user that can access the database can use the function.

    Thanks

    Wednesday, April 28, 2010 5:33 PM
  • You cannot use sa or any other SQL user account to run CRM report or query against filtered views. 

    Better start a new thread, since this is essentially a new question. 

    Cheers,


    Daniel Cai | http://danielcai.blogspot.com
    Wednesday, April 28, 2010 5:47 PM