Alex
Tue Dec 12 10:53:52 CST 2006
John,
All datetime values are stored in UTC, so there will always be an offset
between what is entered/displayed and what is stored. This way, the times
are displayed properly for users across timezones etc.
Where this falls down is when you query the date field in the datebase
directly - if you query the datebase using Advanced Find / Filtered viewthen
it should take any offsets into account for that individual user, but if you
query the database through Query Analyser or a bespoke app then you will
have problems unless you take into affect UTC.
When you check the filtered view there should be both a crm_date and a
crm_dateutc field to use your example. I know that there is only one field
on the EntityExtensionBase table, but at the FilteredView level there will
be 2 fields, 1 with the name of the field and 1 with the name of the field
suffixed by 'utc'. Examine the view and you'll see it uses a function called
fn_UTCToTzSpecificLocalTime to create the second field.
Regards,
Alex
"John C. Marx" <jmarx@pinnacleofindiana.com> wrote in message
news:OBjAAigHHHA.3540@TK2MSFTNGP02.phx.gbl...
> Alex,
>
> My problem is that we have a custom field (example: crm_date) that is
> storing the date. The system though is it saves as say 12/12/2006 02:00:00
> when it was actually entered on 12/11/2006 at 21:00:00. There is only one
> date field in the system and we need to offset it by the user that entered
> the information. Some users are offset -5, others -6 and the daylight
> savings time applies to some, but not all of the users have daylight
> savings time. What we're trying to achieve is we pull in 12/11/2006 as the
> date rather than 12/12/2006 which is what's happening now.
>
> John
>
> "Alex Goss" <alex.nospam@talonsys.nospam.co.uk> wrote in message
> news:%23$cnngfHHHA.4904@TK2MSFTNGP04.phx.gbl...
>> John,
>>
>> Note that there are 2 date columns for every single date field in a
>> Filtered view (e.g. createdon and createdonutc).
>> When you add a custom attribute of type datetime to an entity in CRM,
>> when you save and publish that change the relevant Filtered view should
>> be updated to include that new atribute. The nice thing is it will
>> actually be there twice, the second instance will have the suffix utc,
>> e.g I add a datetime attribute called new_test to the Account entity, so
>> when this is published the FilteredAccount has been amended to include
>> both new_test and new_testutc. Check the relevant Filtered view, you
>> should have both the datetime with daylightsavings applied (and timezones
>> etc) and another attribute without them in utc format. Make sure you are
>> using the correct field.
>>
>> Hope this helps,
>>
>> Alex
>>
>> "John C. Marx" <jmarx@pinnacleofindiana.com> wrote in message
>> news:Oeh9VHfHHHA.2632@TK2MSFTNGP06.phx.gbl...
>>> When doing reports with CRM we have several users in different time
>>> zones (Central and Eastern). To add pain to injury some have daylight
>>> savings time and others don't.
>>>
>>> We've added custom date fields into CRM and are now reporting. Depending
>>> upon when the information is entered it shows on either the current day
>>> or the next day. These dates we need to report on the correct date. We
>>> know we have the SystemUserBase table that we can cross reference for
>>> time zone bias but is there a simple function that we can wrap a query
>>> in to automatically handle the offset? We're using a filtered view to
>>> try and get the information out of CRM in our reports.
>>>
>>> John
>>>
>>>
>>
>>
>
>