Dynamics CRM - Daylight savings time

3
votes

Daylight savings time kicked in on March 27 in Ireland. Since then any records that have been added to Dynamics have had any date fields set to an hour behind the actual time they were added.

What's weird is that the web UI returns the dates correctly, whereas querying using SQL server and XRM return them incorrectly.

The time settings on the server machine are correct, I've installed rollup 16, is there anything else I can try?

Thanks,

David

3
dynamics-crmdynamics-crm-4
Which columns do you select with SQL (on which table or View)? The raw tables, views and the sdk returns date values in UTC. – ccellar
I'm looking at the createdon field in the view. So when I'm reading the values I should probably convert from UTC to local time. – dlarkin77

3 Answers

5
votes

Dynamics CRM stores all date time values in UTC-Format.

All data which is entered with the web front-end gets automatically converted to UTC when stored and converted back to the user timezone when read.

If you are using the Dynamics CRM Outlook Client, the timezone settings of Windows are used. If you use the web-client, it uses the timezone setting in your personal options. Make sure it is correct.

SDK

The SDK always use UTC for dates and does not convert them in any way. It is your responsibility to convert the data in an appropriate way. However, the CrmDateTime class contains some useful methods and properties for that.

Reports

Make sure that you only access the FilteredViews (for example FilteredAccount, which is btw the only supported use of the database). Date fields are automatically converted into the right timezone (according to the user settings). If you need to access the raw value, it is available as column with utc postfix (createdon -> createdonutc). To format date values according to the user/system settings you can access these settings also in reports. See "Formatting Content"

3
votes

Also handy are two UDFs that come with Dynamics CRM:

fn_UTCToLocalTime(@dateTime)
fn_LocalTimeToUTC(@dateTime)

These translate to/from UTC using the system user's declared timezone and DST is taken into account.

0
votes

It seems that my problem is solved by converting DateTime values from UTC to local time.

(I was hoping to mark ckeller's comment as an answer but it seems there is no way to do that. Thanks very much ckeller.)