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:
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:
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:
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
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
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
| 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 |
Option (b): CSV is prepared with date and time, Salesforce will accept following format:
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:
- 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
- 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.
No comments:
Post a Comment