Dates Fields and Time Zones in CRM

As we pick up larger projects, we are finding more that cross international boundaries. Due to the way SQL handles date fields and how the CRM import process works, this presents some problems. Here is the summary of my findings on these issues.

Server time – this is the timezone of the server. So if you server is installed in NZ, you will be in GMT+12
User time – this is set in the user’s CRM options


Daylight saving – note that to make this email simpler, I have ignored daylight saving. When you are working out time adjustments, you will need add time on appropriately. Daylight saving varies by country and even within countries e.g. Queensland does not have it but NSW in the same timezone does.

1. Populating a date and time field through the UI
Date and time is entered as user’s local time and stored as UTC in the database. When viewed by a user, this is converted to the user’s time zone. This means that if you are an NZ user (GMT+12) and you add an appointment for 1100 NZ time, it will appear as 0900 NZ time to a user in the Sydney timezone (GMT+10). So far, so good.

Resolution: No required, working as expected


2. Populating a date only field through the UI
SQL does not have the concept of a date only and has to store date and time for date only record entered in CRM. It does this by storing the date at midnight local time. This means that if a NZ user (GMT+12) adds a date of 09/02/2011, it will be stored in SQL as 2011-02-08 12:00:00 (midday the day before) so that when you add on the 12 hours, it becomes 2011-02-09 00:00:00. Now, if this date is viewed by someone in the Sydney timezone (GMT+10), it will be converted to their local time by adding on 10 hours to give 2011-02-08 22:00:00 which when you display as a date only field shows as 08/02/2011 – the day before the date entered. In summary:

Date Entered by First User (dd/mm/yy) Time Zone of First User (GMT+no.of hours) Date and Time Stored in SQL Time Zone of Second User (GMT+no.of hours) Date and Time as Converted from SQL time Date Displayed in CRM for Second User
09/02/2011 12 08/02/2011 12:00:00 10 08/02/2011 22:00:00 08/02/2011

Therefore, whenever your timezone is ahead of someone else’s, the date only field when viewed by the other user will always display the previous day (as any number of hours subtracted off of midnight will always be the previous day). This will only be a problem where dates entered in one time zone need to be viewed in another. For example, if an admin in NZ adds Christmas Day to all calendars, it will be on 25 December in NZ and 24 December for everyone else. Note that anyone whose time zone is ahead of yours will always have the correct day (as you will never have to add on more than 24 hours).

Resolution: If the data can be entered locally then you will not experience this issue. If the data must be entered from another time zone, then you will need a plugin on the record to convert the date and time to equivalent of 11pm NZ time (see 4 below for why we use 11pm). Note that the user will never see the time component as this field is displayed as date only in CRM.


3. Populating a data and time field using data import
When importing data, CRM uses the zone of the user importing the data.

Resolution: The simplest fix is to change the timezone of the user importing the records and limiting the records to a single timezone. If you need to add multiple records across time zones in one load (e.g. when loading appointment data for all of Australia), you will need to convert the time to as it would appear in the loading user’s local time. For example, if you are a NZ user (GMT+10) and want to load an appointment for a Sydney user (GMT+10) to show in their calendar from 1000 to 1100, you will need to import it as 1300 to 1400 (the GMT+12 equivalent time). See 4 below if you want to know how to add or subtract hours from a field in Excel.


4. Populating a date only field using data import
If your user is in NZ and you are loading data for any other timezone than it will display the previous day (same logic as 2 above). If, for example, you are loading sales records for Australia which use a date-only transaction date to your NZ based server, then when these records are viewed by an Australian user, they will show a day earlier. The same problem will exist when you run reports – transactions listed on the 1st of a month will get moved to the previous month – which will mean all your figures are slightly out.

Resolution: If a field is configured as date only in CRM, you can still load a time component to this field. For New Zealand, you should add 23 hours so that no matter how many hours you are behind NZ, a date only field will always show the same date (even when adjusted for Daylight Saving although this is not included below).

Hawaii US West Coast (e.g. Redmond) US East Coast (e.g. New York) London Hong Kong Sydney NZ
GMT-9 GMT-8 GMT-5 GMT+0 GMT+8 GMT+10 GMT+12
2:00 3:00 6:00 11:00 19:00 21:00 23:00
Incidentally, If anyone needs me to fly out to Hawaii to rectify time zone issues, I’ll make myself available 

To convert a date field in Excel, first format it to a custom formatting of ”dd/mm/yyyy hh:mm:ss”, then use the formula “=[Date Field] + Time (23,0,0)” where [Date Field] is the reference to the field to which you want to add time. A date that started out as 09/02/2011 will then end up as 09/02/2011 11:00:00 p.m. If you are saving your data as a CSV file, you must ensure the formatting is

Note that if you are in any other timezone, the no of hours to add is the number of hours you are ahead of the international date line minus 1

===============
Hi B,

Thanks for a great summary of the date headaches and possible solutions.

Given the problems with them I’m struggling to think of a reason why you would want to design a globalised system to record a date only and not the date and time.

Say that I am a resource NZ and I’m celebrating Christmas then someone in UK will want to know that I’m not available (a.k.a drunk) between midday of the 24rd until midday 25th UK time, not that it is Christmas day on that day. Also if someone in the UK wanted to send me a birthday wishes email they would need to be reminded to send it the day before to reach me in NZ on my Birthday.

Similarly for a deadline, if a task needs to be completed by Wednesday and the task creator is in NZ, then from their perspective the task should be completed by COB Wednesday NZ time regardless of where the task is being carried out. So while is tempting to make the due date Wednesday the task should have a due date-time of Wednesday at 5.30pm in NZ and Wednesday 3.30pm in the Oz. It is tempting to

Also if I understand you solution of adding 11 hours correctly, it will only work for countries that are further east on the globe than GMT + 2, fine for Oz but not ok for the west of this line UK, USA etc.

Cheers
A

=================
Hi A,

I agree that specific tasks which need completed at a specific time should always be date and time.

With the Christmas Day example, to mark this in everyone’s calendar correctly, you would need to create one entry for every timezone called NZ Christmas, NSW Christmas, Queensland Christmas, SA/NT Christmas, WA Christmas etc and you would either need to convert all times to NZ equivalent time or change your timezone before creating each of these if adding them as date only.

The problems occur when you have a global system which is administered and maintained out of one country (and NZ is the worst case scenario). For example, we have to load sales data with a date only field (customer does not have sales time, only date and has no interest in seeing a time field) for all global markets from NZ. These have to be manipulated to show on the correct date for the timezone.

You don’t add on 11 hours, you add on 23 hours, to make it 11pm as per my example below.

Kind regards,

B

Source - Intergen