Having recently worked on a project where time values are critical in Date/Time fields in Dynamics CRM, I came across an issue where Date/Time fields were appearing correctly through the CRM interface but abnormally when running reports.

The Issue

Dynamics CRM stores Date/Times in UTC and shows them to a user in the time zone their User account is set to, so in the case of a User in the Perth time zone:

dynamics crm set time zone

CRM will add 8 hours to the UTC time.

Daylight Saving time was trialled in Western Australia for 3 years between 2006 and 2009 between the following dates:

  • 03/12/2006 to 25/03/2007
  • 28/10/2007 to 30/03/2008
  • 26/10/2006 to 29/03/2009

Looking at a record in CRM between these dates will show the correct date time:

dynamics crm set time zone 2

However looking at the date directly from the Filtered View (used in reporting) will show:

dynamics crm set time zone 3

As you can see, the times are 1 hour behind as the filtered view hasn’t taken into account the Daylight Saving Time.

The Fix for the Time Issue

The fix comes in 2 parts depending on whether all your users are in the Perth time zone or not.

If all your users are in the Perth time zone, then you can apply a simple case statement to any date/time fields that are affected:

 

The Case statement adds 1 hour to the time if the date falls between the Daylight Savings Ttime dates.

If your CRM has users are in time zones that are not just Perth, then you will need to check the users’ time zone in the FilteredSystemUser table:

 

Any user who does not have 225 as their time zone do not need to have the fix applied.  This can be added to the SQL statement in the Report.