Salesforce: Data Loader Time Zone

For most Salesforce administrator and consultant, Data Loader is a familiar tool when work with data. Data Loader is a simple yet powerful tool provide by Salesforce to insert, update, upsert, export, and delete data in Salesforce. To insert, update, upsert and delete Salesforce record, you need to prepare the data in CSV file format. After execution, it will provide you success and error log in the CSV format as well.

But, many users not realize about Time Zone setting in Data Loader. By default, after you install Data Loader, it will follow your computer time zone. This will cause issue when you load data for Date or Date Time field.

If you refer to my blog earlier regarding upload Quota, where Start Date in Revenue Quota is not correctly loaded, this is one of the sample caused by incorrect Time Zone setting in Data Loader.

In this exercise, we are not turning on "Use European date format (dd/mm/yyyy)".

Scenario 1: insert or update Date field in Salesforce:

• You set Data Loader time zone to eastern GMT time zone (from GMT+01:00 to GMT+14:00), example: Asia/Singapore GMT+08:00
• You provide the data in CSV with date only (format yyyy-MM-dd), without time, example: 2015-02-14

Result: this will cause the date auto adjust to 1 day earlier of the date specify in CSV file.

Analyse: even for Date field (not Date Time field), if you do not provide time in the CSV file, it will treat the time as 00:00:00. So, 2015-02-14 added with time 00:00:00 for Asia/Singapore time zone, it is equal to 2015-02-13T16:00:00 GMT. In Salesforce, it will stored as 2015-02-13, instead of 2015-02-14.

Solution:

• Add time x hours as your time zone setting in Data Loader, example: 2015-02-14T08:00:00.000 SGT
• To make our life easier, change Data Loader time zone setting to GMT, you do need to add time anymore.

Scenario 2: insert or update Date Time field in Salesforce:

Option (a): CSV is prepared with date only, Salesforce accept the date in 3 formats below:

• yyyy-MM-dd
• MM/dd/yyyy
• yyyyMMdd

In this exercise, Salesforce user time zone is (GMT+08:00) Singapore Standard Time (Asia/Singapore). Let us see how is the result, when Data Loader time zone is set as GMT and as Asia/Singapore.

Source	GMT	Asia/Singapore
2015-02-16	2/16/2015 8:00 AM	2/16/2015 12:00 AM
02/16/2015	2/16/2015 8:00 AM	2/16/2015 12:00 AM
20150216	2/16/2015 8:00 AM	2/16/2015 12:00 AM

Summary from this exercise, if we load date time field without time given, set Data Loader time zone the same as User time zone to get 12:00 AM

Option (b): CSV is prepared with date and time, Salesforce will accept following format:

• yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
• yyyy-MM-dd'T'HH:mm:ss.SSS SGT
• yyyy-MM-dd'T'HH:mm:ss.SSSSGT
• yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00
• yyyy-MM-dd'T'HH:mm:ss
• yyyy-MM-dd HH:mm:ss
• yyyyMMdd'T'HH:mm:ss
• MM/dd/yyyy HH:mm:ss

and NOT following format:

• yyyy-MM-dd'T'HH:mm:ss.SSS Singapore Standard Time
• yyyy-MM-dd'T'HH:mm:ss.SSSSingapore Standard Time
• yyyy-MM-dd'T'HH:mm:ss.SSSGMT-08:00
• yyyy-MM-dd'T'HH:mm:ss.SSS -800
• yyyy-MM-dd'T'HH:mm:ss.SSS-800

Source	GMT	Asia/Singapore
1) 2015-02-16T09:00:00.000Z	2/16/2015 5:00 PM	2/16/2015 5:00 PM
2) 2015-02-16T09:00:00.000 SGT	2/16/2015 9:00 AM	2/16/2015 9:00 AM
3) 2015-02-16T09:00:00.000SGT	2/16/2015 9:00 AM	2/16/2015 9:00 AM
4) 2015-02-16T09:00:00.000GMT+08:00	2/16/2015 9:00 AM	2/16/2015 9:00 AM
5) 2015-02-16T09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
6) 2015-02-16 09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
7) 02/16/2015 09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
8) 20150216T09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM

Summary:
1) both time zone will return 5 PM, because Asia/Singapore = GMT+08:00 plus 9 hour = 5 PM
2 - 4) both time zone will return 9 AM, because we have defined time zone in the source data
5 - 8) GMT setting will return GMT+08:00 (this is user time zone) plus 9 hour = 5 PM, while Asia/Singapore setting will return GMT+08:00 (this is user time zone) minus Asia/Singapore (this is Data Loader setting time zone) plus 9 hour = 9 AM

Following information cite from Data Loader guide. If a date value does not include a time zone, this value is used:

• If no value is specified, the time zone of the computer where Data Loader is installed is used.
• If an incorrect value is entered, GMT is used as the time zone and this fact is noted in the Data Loader log.

The value can be a full name such as America/Los_Angeles, or a custom ID such as GMT-8:00


Reference:

• Salesforce Supported Time Zones
• Data Types Supported by Data Loader
• How can I ensure my dates are correct when loading with the Data Loader?