Data loader: Date and Time Zone Problem

Feedback

As we all know that data loader is a tool that use to export and import data over the salesforce, it is used for inserting,updating,deleting and upserting the data while export and export all are use to export data from salesforce.This post we would talk about Time Zone related problems in the data loader and how to encounter those problems.if you ever try to insert or upload data in date fields over the salesforce organization then you might have faced the problem that either data is inserted before one day from the actual date in the Excel sheet or may be month and data are get swapped with each other while inserting the data in the salesforce organization, so lets see how to encounter those problems.

Date And Time Formats

Before moving ahead to the date and time problem in data loader, we should know the format of date and time as in my case i was always get puzzled when ever i saw alphabets ‘T’ and ‘Z’ in the date formats, see a example below.

YYYY-MM-DDThh:mm

By looking at the above format of date and time we can infer that YYYY stand for year,MM stand for month and DD stand for the Date now after the date we see a completely different character which is T which indicate the beginning of the time element, So element ‘T’ is just used for indication that times values are starting from here.

The YYYY-MM-DDThh:mm format value would like to be 2015-01-09T12:49:00.

Another thing I want to discuss is ‘Z’ and ‘TZD’ characters in date and time formats but before looking at those thing we should know about the ‘UTC’.UTC is called as Coordinated Universal Time and it was formerly known as ‘GMT’, Consider that our earth have a clock which is set at a time 00:00:00 and in comparison to clock the time zone of the various countries are concluded for an example see below date and time format.

YYYY-MM-DDThh:mm:ssTZD

Here ‘TZD’ is indicating difference in time between UTC and a particular country, the time zone of India in comparison to UTC is +5:30 means India is ahead of UTC by five hour and thirty minutes,so that why whenever we set the time format in windows we set it in comparison to the UTC,see below picture.

time of countries in comparison to UTC

‘TZD’ represent the time difference and a particular day in India in UTC format would be written as 2015-01-09T12:00:00+05:30 and which can also be written as 2015-01-09T17:30:00Z , so the word ‘TZD’ and ‘Z’ can be used interchangeably.

Case 1: Inserting Date And Time Value In Salesforce

We want to insert the date and time values in salesforce but after insertion there would always be a time difference between the output and input values, Lets try to understand this problem with an example

Scenario

I am salesforce user and my time zone in salesforce profile is set to Japan Standard Time zone which is ahead of +09:00 from UTC time,see below picture for more details.

time zone in user profile

My system time zone is set to the Qatar time zone which is ahead of +03:00 from UTC time, see below picture for details.

system time zone

My data loader time zone is also same as my system time zone means set to Qatar time zone, see below picture for more details.

data loader time zone

I have an excel sheet and my input date and time value is ‘9/1/2015 4:32:00 PM’ so when i imported this value in salesforce the resulted output in date and time field would come as ‘9/1/2015 10:32 PM’ , there is a increase of the six hours from input to output value and the reason behind the change in input and output values is because we have different time zones for the user,system and data loader.Lets understand how the actual output values are coming up.

Explanation why the date would come six more hours than input

1.) If time zone of a user in salesforce is set to japan time zone or any other time zone then default value in date and time field would always be plus/minus or in respect to the user time zone which is set in salesforce.

As per our scenario: My Input date and time is ‘9/1/2015 4:32:00 PM’ and my time zone in salesforce profile is set to japan time zone which ahead of nine hour so when i try to import this it would become as ‘9/1/2015 13:32:00 PM’

2.) My data loader time zone is set to Qatar time zone which is ahead of three hours of UTC so when we insert the values through data loader this ahead values get deducted so,

As per our scenario:This 03:00 value would get minus from the ‘9/1/2015 13:32:00 PM’ value and the resultant output would come as ‘9/1/2015 10:32 PM’.

Possible solutions to the Problem

The best solution to the problem is change the time zone of the salesforce user to GMT,See below picture for more details.

change the user time zone in salesforce

The last we need to do it to change the time zone of the data loader to GMT/UTC,See below picture for more details.

time zone setting in data loader

Now if you inserted the date values it would comes correctly in salesforce,I hope you like my this post if there would be any query related to post please post below in the comment section i would further write the second part on the dates and time formats in salesforce and don’t forget to subscribe to my blog and social networking pages.

citation
1.) http://www.w3.org/TR/NOTE-datetime
2.) https://help.salesforce.com/HTViewSolution?id=000004680&language=en_US

Related Articles :

Speak Your Mind

*