locked
Date issue RRS feed

  • Question

  • Hi

    We have recently bought scribe replication software.

    We dynamics CRM 2013 online which gets connected to our database SQL server 2005 through scribe

    On CRM 2013 we have a entity called Programme Profile (new_programmeprofile in SQL)

    In this entity we have a fiedls called Claim Start Date (new_claimstartdate in SQL)

    We i look at the data from Advanced find in CRM it comes right as below

    Claim Start Date

    01/05/2013        

    01/07/2013        

    01/08/2013

    01/10/2013        

    01/12/2013        

    01/01/2014        

    01/02/2014        

    01/03/2014        

    01/05/2013        

    01/07/2013        

    01/08/2013        

    01/10/2013        

    01/12/2013

    01/01/2014        

    01/02/2014        

    01/03/2014        

    But when the same data is viewed in SQL the Calim Start date (new_claimstartdate) goes wrong by an hour

    new_claimstartdate           

    30/04/2013 23:00:00.000                   

    30/06/2013 23:00:00.000                   

    31/07/2013 23:00:00.000                   

    30/09/2013 23:00:00.000                   

    01/12/2013 00:00:00.000                   

    01/01/2014 00:00:00.000                   

    01/02/2014 00:00:00.000                   

    01/03/2014 00:00:00.000                   

    30/04/2013 23:00:00.000                   

    30/06/2013 23:00:00.000                   

    31/07/2013 23:00:00.000                   

    30/09/2013 23:00:00.000                   

    01/12/2013 00:00:00.000                   

    01/01/2014 00:00:00.000                   

    01/02/2014 00:00:00.000                   

    01/03/2014 00:00:00.000                   

    Field Claim Start Date is Date only feild

    This is a very urgent query  i am not sure why only some date in SQL goes wrong

    In scribe in Schedule tab i used GMT (Cordinated Universal Time)

    and GMT Greenwitch mean time but still the date goes wrong in SQL

    Any help on this is much appreciated

    Thanks

    Pradnya


    Pradnya07

    Tuesday, November 19, 2013 3:05 PM

Answers

All replies

  • Sadly, this is a known issue due to how CRM handles 'date-only' fields. They will display as different days depending on what time zone you are in, daylight savings, etc.

    I recommend finding items on Connect that have the most existing votes and continue to upvote and perhaps someday this will get addressed so that we can actually store a 'date' without any timezone or time information (like a birthday) without having to use a text field or do some tap-dancing like entering all date-only fields as 10:59:59 am so that they calculate to the same date in MOST timezones.

    For example, https://connect.microsoft.com/dynamicssuggestions/feedback/details/471412/crm-dates-should-be-stored-as-dates-not-times-date-only

    • Proposed as answer by Wayne Walton Tuesday, November 19, 2013 4:25 PM
    Tuesday, November 19, 2013 3:57 PM
  • Thanks for your reply but i cant access  your given link 

    Pradnya07

    Wednesday, November 20, 2013 9:10 AM
  • I think you are querying the tables and not the FilteredViews, try do for example
    SELECT * FROM FilteredAccount

    and check the dates

    My blog: www.crmanswers.net - Rockstar 365 Profile

    Wednesday, November 20, 2013 9:52 AM
  • Thanks for your reply

    You are right i am accessing the table how do i create view in CRM 2013 online version

    I use scribe software to create/update tables in the database


    Pradnya07

    Wednesday, November 20, 2013 3:24 PM
  • there is no need to create views. every system or custom entity having filtered views in dynamics CRM online / onpremise.

    name like that : filtered+entityname

     

    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    Wednesday, November 20, 2013 3:34 PM
  • Thanks Vikram then how do i solve my utc date issue while running SQL query

    Pradnya07

    Wednesday, November 20, 2013 3:36 PM
  • Please check following links

    http://www.develop1.net/public/post/Dynamics-CRM-DateTimes-the-last-word.aspx

    http://icircusmonkey.wordpress.com/2012/02/29/ssrscrm-localutc-datetime/


    Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
    Vikram !

    • Marked as answer by Simran08 Thursday, November 21, 2013 2:49 PM
    Wednesday, November 20, 2013 3:49 PM
  • Thanks a lot for al your replys

    Pradnya07

    Thursday, November 21, 2013 2:49 PM