+ Start a Discussion
RanchoRancho 

Data Loader subtracts 4 hours from Date

Hi ,

 

Am loading a huge chunk of data to SFDC . While doing this , am parsing the dates from Oracle source table as

“yyyy-mm-ddThh:mi:ss.000z” (hh = 24 hour format) . Somehow when the record is loaded into SFDC , the dates appear as “Time - 4 hours” . Is there some setting in the Org or the dataloader ?

 

Having searched the SFDC community , I tried testing various date formats in the sandbox Org . The Org setting says Company Hours = "GMT-6:00" Hours.

 

 

If I load 10:00 (AM) ->  that goes into SFDC as 06:00AM  -> it subtracts 4 hours

If I load 17:00 -> that goes into SFDC as 12:00 PM  -> it subtracts 5 hours

 

Its inconsistent in adding the hours . am not finding a pattern too.

 

 

I tried another format:

 yyyy-mm-ddThh:mi:ss.000EDT  -->  this loads the perfect time IF the time is in AM (before 12 noon)

This does NOT work if the time is past 12 noon.

 

If I load 17:00.000EDT ->it loads  as 04:00 PM and NOT as 05:00PM. 

if I load 10:00.000EDT  -> it loads it correctly.

 

FYI - both dates are between april to June. I haven't touched the testing of Daylight Savings yet.

 

 In all the above cases the Dates go in Correctly, only the Time gets messed up. Please help .

 

Any suggestion on how do I overcome this ?

 

 

Nick1746323Nick1746323

The org timezone wouldn't affect this, whats your user timezone set to (in salesforce)?

 

And can you post the actual CSV data for these two examples?

 

RanchoRancho

My User timeZone is set to Eastern Time.

 

CSV data contains id , date thats it. 

'2009-11-23T10:14:10.000z'

 

in all other formats which I had tried I removed the 'z' from the end and entered 'EDT', 'EST','CDT','CST','GMT' etc.. 

 

none loaded the same time.

Nick1746323Nick1746323

 

There is some information here on what formats are supported 

https://na1.salesforce.com/help/doc/en/salesforce_data_loader.pdf

 

 

The 'Z' on the end means GMT so it would be normal that the times appear to you as 4 or 5 hours different, depending on the time of year. But if indeed all your dates are between april and june, there must be some other explamantion :/

RanchoRancho

ok. As you say my format is GMT , so according to the Org's company hour setting, it should set as "GMT - 6 hours " (since the company is in Central time). It does not do that too.

 

My user is Eastern Time and I work on Eastern time too, so it should either use my time zone. 

 

What is the viable solution for this ? 

Nick1746323Nick1746323

Well you just need to match the timezone of your oracle data to the timezone used by the data loader.

i.e. if your data from Oracle is GMT then load it using the 'Z' format. If all your time data comes out from Oracle as EST then you must format it according to the documentation, which recommends the format:

 

yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm

 

 

e.g. your data should look like

 

"2009-11-25T11:48:00:000-0500"