locked
Date Time in CRM RRS feed

  • Question

  • HI All,

       When I do the following query using SQL to get some records from database:      

    select

     Va_Rollover_Next_Date from Va_pledgeExtensionBase where Va_Rollover_Next_Date = '28 July 2010' 

              OR  

    select Va_Rollover_Next_Date from Va_pledgeExtensionBase where Va_Rollover_Next_Date = cast('2010-07-28' as date)

    I get only 1 record.  But If i do the same query using Advanced Find i get 3716 records which is correct. I get 3716 records from sql query too if I change the date to one day back which is 27 of July "select Va_Rollover_Next_Date from Va_pledgeExtensionBase where Va_Rollover_Next_Date = cast('2010-07-27' as date)"

     

    Please let me know what's the reason and how to come over it , I looks to me as IF in sql date is with universal time but when CRM display it calculated the datetime according to local time?

     

    Thanks

    Muhammad Hassan Farooqi

     


    Hassan
    Thursday, July 29, 2010 12:17 AM

Answers

  • My guess was right - use this function to 'fn_UTCToTzSpecificLocalTime' to convert time when retrieving records based on date time as can be seen in Views

     

    Thanks

    Muhammad Hassan Farooqi


    Hassan
    • Marked as answer by Hassanz Thursday, July 29, 2010 12:57 AM
    Thursday, July 29, 2010 12:57 AM

All replies

  • My guess was right - use this function to 'fn_UTCToTzSpecificLocalTime' to convert time when retrieving records based on date time as can be seen in Views

     

    Thanks

    Muhammad Hassan Farooqi


    Hassan
    • Marked as answer by Hassanz Thursday, July 29, 2010 12:57 AM
    Thursday, July 29, 2010 12:57 AM
  • Correct Hassan; datetime is stored in UTC.

    You'll have to do a correction yourself if you need local time; something like:

    DATEDIFF(MINUTE,GetDate(),GetUTCDate());
    will give you an offset with which you can convert a given date to its UTC equivalent.

    --pogo (pat)
    Thursday, July 29, 2010 1:03 AM
  • Also, consider querying the filtered views rather than the base tables.  You shouldn't need to use any conversion methods when querying the filtered views.

    Regards, Donna

            Windows Live Blog

    Thursday, July 29, 2010 1:39 PM