When migrating from a source system to Salesforce using the Apex Data Loader a date becomes the date it was minus 1 day. This happens for all of my objects that contains a date.
The source system is MySQL but the data originally comes from Lotus Notes.
Example:
We are migrating customer information into an Account. In MySQL a query generates x rows where a specific date is one of the field values. The rows are via the Data Loader migrated into an Account using upsert.
| Row in MySQL | ||||
| Company name | Customer start date | Category | ||
| Company A | 2009-04-23 | A | ||
| Corresponding values in Salesforce after migration | ||||
| Company name | Customer start date | Category | ||
| Company A | 2009-04-22 | A | ||
Since this causes problems of different kinds for example when creating reports and just the fact that we get wrong data in the system I would like to have it fixed. Anyone know of a simple explanation?
During our discussion internally we were thinking that it might be a time zone issue between Sweden and America. I have asked Salesforce for an explanation and I will update this post when I get it.
Update:
I got an answer from Salesforce with an explanation on why it works the way it does regarding the above issue.
The DataLoader is running on Java which uses the installed machines default time zone. In Salesforce Dates need to be imported in GMT so that dates will not appear a day out.
Dates need to be loaded with a GMT time component in the following format: YYYY-MM-DDT00:00:00.000Z, e.g. for 18 July 2009 => 2009-07-18T00:00:00.000Z
Related posts:
{ 13 comments… read them below or add one }
Hi…
I m getting same error……while importing data using Apex Data Loader, Can please tell me in detail what I have to do next…
Please Help…….
hi Niklas
I tried option 1. I changed date format in my csv file into ISO YYYY-MM-DDT00:00:00.000Z, but still I am getting same result………will I have to change format any other place……
Great help – thanks.
Also running this in Daylight Saving (before Oct 25, 2009) hours seemed to make a one hour difference and put the dates back a day, if you used YYYY-MM-DDT01:00:00.000Z all would be well again (ie. 1AM on the date in question).
Now, as we are out of Daylight Saving hours again plain old YYYY-MM-DDT00:00:00.000Z should work again.
Please suggest how to import old dates in salesforce created date field using data loader because it is taking the sales force default created date . i want to overwrite the created date field by the one i m having in the csv
Manish,
For Importing the legacy created date into sfdc Audit field ‘CreatedDate’, You have to enable ‘Create Audit Fields’ on your Org
Hi Hermant,
As I see it you have two options:
1. Import dates the way suggested by Salesforce which were explained in the update above, i.e. on the format YYYY-MM-DDT00:00:00.000Z, e.g. for 18 July 2009 => 2009-07-18T00:00:00.000Z
2. Manipulate the dates you are using to current date plus one day.
But I would definately recommend option 1.
Do you have the possibility to manipulate the data before importing it with the data loader in a good way?
Doods,
Downgrade your Apex data loader version to 19.0. No dates issue!!
In the new version (20.0), in the setting menu, there is an extra option ‘TimeZone’. That makes the difference
Haffunnn
That’s a good one Manish!
Thanks, I changed the format of the date and it worked fine.
Hi saikrishna ,
How did you changed the fomat of the date ?
thanks,
Thank you very much Manish, that was the detail no one else mentioned. Even with the correct format, the Timezone setting will make the date incorrect!
Hi Manish..
There is another major date issue with any version of apex data loader.
If DD/MM/YYYY is the format in the csv file, then the date loaded in salesforce is unbelievably different…! Ex: 26/10/2011 is the date in my csv file and the date is loaded in salesforce as- 2/10/2013 .. what makes such a huge difference??? Is it like csv file doesn’t support this date format? pls respond..
Regards,
Anil